LinkBack URL
About LinkBacks
Is there a formula that will calculate the number of hours worked between 8PM and 5AM? I need it for a time card.
Is there a formula that will calculate the number of hours worked between 8PM and 5AM? I need it for a time card.
=MOD(end_time-start_time,1) and format as required
Pepe,
I tried what you suggested, but I don't seem to be able to get the correct answer. Maybe I'm not formatting it right. How/what do I need to format? On a side note, my in/out times are formatted with dates. I'm not sure but I think that makes a difference.
Bob
TimeDiff.xlsxDoes the attached help? ( only works if less than 24 hrs apart)
Well that calculates the total number of hours between any two hours, but what I need it to do is calculate just between 8PM and 5AM
Well then replace the hours as needed !
I have two columns in my Excel file that contain the Start(C) and End(E) times, I was able to get the time difference between the 2 cells. Their cell format is dd/mm/yyyy 00:00 PM/AM.
I need a formula that will calculate the number of hours worked during the night shift (between 8PM & 5AM), but I can't figure it out.
For Example,
Start Time (C12): 1/2/2012 3:00 AM
End Time (E12): 1/2/2012 10:00 PM
Night Differential should be 4 hours. 3:00AM -5:00AM is 2hours and 8:00PM-10:00PM is another 2 hours.
Right now I have this formula.
=MAX(0,MIN(E12,INT(C12)+1+"5:00")-MAX(C12,INT(C12)+"20:00"))
However it only calculates the Night differential that comes from the back end of the shift. For example it can calculate the 2 hours that come from working 8:00PM-10:00PM, but not 3:00AM-5:00AM.
Any help would be appreciated. Sorry if my prior wording of this issue wasn't great!
Sincerely,
Bob
Hello Bob, try this formula
=E12-C12-(INT(E12)-INT(C12))*("20:00"-"5:00")+MEDIAN(MOD(C12,1),"5:00","20:00")-MEDIAN(MOD(E12,1),"5:00","20:00")
Audere est facere
This is the formula I'm working with now. It removes one of the issues with the one I posted earlier.
=IF(COUNT(C12,E12)<2,0,MAX(0,MIN(E12,INT(C12)+1+"5:00")-MAX(C12,INT(C12)+"20:00")))
Seems to be working perfectly!!! Thank you so much daddylonglegs! I really appreciate it.
Bob
This formula works great! I had asked the same question and kept getting the same answer not understanding what I was really asking before I stumbled upon this one.Originally Posted by daddylonglegs
I know this is a really old thread, but just wondering if there's a way to make it work for an overnight shift?
Last edited by kiwisoup; 03-10-2022 at 07:42 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks