Forum Discussion
Formula for Subtracting End time from Start time to a Number
Let's say start time is in C2 and end time in D2.
The number of hours is
=24*MOD(D2-C2, 1)
Format the cell with the formula as General or as Number with the desired number of decimal places.
Hans, why did you add the MOD?
if it is possible they have days included with the time then I would think he would want that included
e.g. (2025-01-02 18:00) - (2025-01-01 13:00) should be 29 hours not 5 hours and if they are always the same date (or no date) then I don't think MOD should matter but maybe I'm missing something.
also, if there is no date portion but overnight is possible (e.g. 11pm -> 2am) then try:
=24*(D2 - C2 + (D2<C2) )
HI Guys, thanks for your help.. I have another dilemma... I know what I want but not how to write the formula.... I've attached the spreadsheet.... the timesheet calculates hours for pay, but the first hour is $50 and subsequent hours are at $35/HR -- So I added some columns to do the calculations... $50 in one column to be added as when the person enters hours.... then a formula in the next column taking total hours ..ex.. (J5 - 1)*35 to get he balance of the total amount due.. then add the $50 plus the next column -- It works fine, but when there are no hours populated yet.. and the 50 hasn't been entered, the total column is showing -$35 because the formula is calculating (0 - 1)*35 I'd like to show 0 in the total column so when I sum it to submit the hours, it's not subtracting all those -$35 amounts.
Thanks!
I assumed that the times didn't include a date.
I find MOD(D2-C2, 1) slightly more elegant than D2-C2+(D2<C2) to handle ending times after midnight, but both work equally well.
ohhhhhh, I see, that MOD is specifically for that overnight 'roll-over'. clever. I wasn't thinking of it that way. I think I will stay with the other version in case dates are present
Hi Hans, I have another formula issue... I need to calculate hours by $, but the 1st hour is $50 and subsequent hours are $35/Hour. I built a column to add $50 which is populated when hours are entered, but I built a formula for (Total Hours - 1)*35 to calculate the other hours. Then I add the two columns together.. Everything works fine except when no hours are entered the Subsequent hours field is showing -$35 because of the x-1*35 formula. I want it to render 0 when there are no hours entered so as I sum that column to get the 'amount due' It's not subtracting the -$35.. I've attached the spreadsheet to make more sense... can you help?
