How to Calculate Overtime Hours on a Time Card in Excel

[KraftyGrandma]

Hi Danny,

Thank you for your video I understand much more now but I'm still in a state of confusion.

The spread sheet is great but still does not give me what I need.

My working week is 37.5hrs but if I only work 36hrs I need to see a minus figure of -1.5hrs is this possible?

[DannyRocksExcels]

@KraftyGrandma

Thanks - I am pleased that I could help you.

re: seeing a Minus Time Value - Excel does NOT like this. In fact, if you perform a Time Calculation that results in a negative number, Excel places ######## symbols in the cell!

My solution is to Label 2 cells 1) Hours worked over 37.5 and place an IF Statement there. 2) Hours worked LESS than 37.5 and put an IF Statement there. Both formulas will return POSITIVE Numbers / Hours.

Change formatting for cells as needed.

Danny

[neeta25011958]

@DannyRocksExcels Dear Danny your video is a great help, however like kraftyGrandma I simply dont understand how to do the minus part. Do you have a video showing me how to do the solution to label the 2 cells. You are heaven sent and thank you for your time and efforts you put to help us..pls help me do the time sheet with the minus...am struggling thank you and god bless

[DannyRocksExcels]

@neeta25011958

Thank you. I am pleased that you enjoy my video tutorials.

Re: "Minus" time or "Negative Time Values" - Excel does NOT like them. If you attempt to enter a Negative Time Value or use a calculation that produces "negative time," Excel will fill the cell with an unlimited number of ########. For example, calculating time that "Spans Midnight." for a "Graveyard Shift" or a "Red-eye" airline flight.

Re: A "Label" that is simply a text entry such as "Start Time."

[sankalp2011]

i tried to make regular hours by applying hh:mm but instead it comes as 21:20 P.m , formating cell is not working and I am using office2007

[DannyRocksExcels]

@sankalp2011

21:20 P m - That is a "TEXT" entry. If you want to enter 9:20 in the evening, enter 9:20 (space)p and Excel will convert that to 9:20 PM - Do NOT use any .(periods) and do NOT include and (space) between P M. If you do, then you will get a TEXT result.

Are you trying to calculate the number of hours worked? If so, send me some additional information and I will try to help you.

Danny Rocks

The Company Rocks

[FORZA0VELOCITA]

Good sir, you just made my life a whole lot easier!

Thank you!!!



[DannyRocksExcels]

@FORZA0VELOCITA

Thank you for offering your feedback. I am pleased that I could help you with my video tutorial.

Danny Rocks

The Company Rocks

[KhurrumIqbal1]

Mr. Danny

One more thing to this video if overtime starts beyond or within/without the standard time (i.e. from 8:00 am to 5:00 pm). Sometimes the nature of work demands to work 24 hours. My Question is, how to extract overtime within 24 hours. It could be 5:00 am to 10:00 pm therefore the total Overtime hours will be [(8-5)+(10-5)]=8 hrs

Time starts at 5:00 pm and ends at 5:00 pm (next day). OT= 24-8=16 hrs

Time starts at 4:00 am and ends at 9:00 pm (same day) OT=[(8-4)+(9-5)]= 8 hrs

KI.

[DannyRocksExcels]

@KhurrumIqbal1

Give me another day or two to create a video that demonstrates this for you. Calculating time that "crosses midnight" presents a particular challenge. Fortunately, there is a formula that will perform the correct calculation.

To be clear: you want to count "any work hours" above 8 hours in "any 24 hour period" to be considered as overtime.

Is that correct?

Danny Rocks

The Company Rocks