Formula Help

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I need a formula to calculate overtime. I have this spredsheet that tracks everyones time, a regular day is 8 hours, anything above that is overtime, any time worked on the weekends is also overtime. So basically whenever a number greater than 8 is entered from Mon to Fri, the difference is considered ot. When ever any number is entered on Sat and Sun that number is also ot.

I would like the ot hours to add up in a different column.

eg. a person time for Sun to Sat is, Sun 5, Mon 8, Tue 10, Wed 8, Thur 12, Fri 8, Sat 0. The ot for this person is 11 hours.

Thanks



2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
https://authentification.site/files/23562832/SAMPLE1.xls

Please see attach sample. If a person works more than 8 hours, then the difference of hours is considered ot, those hours are then placed in a separate column, if that same person works more than 8 hours the following day, then the difference is added to the column with the ot hours, etc.....
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You need to provide every thing since you already have so many macros that do this and that. also when you upload the file, could you either provide the password for the file or have it removed.
https://authentification.site/files/23589903/SAMPLE1.xls

I have removed the password, sorry about that, I forgot. See attached, let me know if this works for you.
Greatful for any help.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
OT based on what ? Week or month. If week then when a week starts and when it ends
perfect, ur the best !

I posted thread a few days ago regarding building a macro. Not sure if you saw it.
Hello Rizvisa,

The formula does not work when a letter is entered in the range. Can you adjust please.

https://authentification.site/files/23755382/SAMPLE1.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I think it is about time to to act on proverbial "Teach a man to fish"

Have you looked at the formula ? If you look at the formula you would notice two things
SUMIF and SUMPRODUCT. If you use help to see what these formula do, you will find that they deal with numbers. So there is your issue. You cannot have a non number there.
Ofcourse I looked at the formula! I understand that, but what I'm asking is if there is any way to get around that, since the range of cells that the formula will uses to calculate a answer will always have letters as well as numbers. No need to be rude.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I was not being rude, or at least no such attempt. Being hyper sensitive to any perceived slight is not not a good thing to have. What I was trying to to do was to explain to you the issue with the formula.

There are too many potential scenario that you have mentioned (based on your past posts) that I have lost track and is a hindrance in giving you any answer. Also what is not clear is what to do if there is a text in one cell.