You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I would like a formula to caculate the number of hours between 2 times. With a start time before midnight and an end time after midnight .ie:If cell B2 is start time & cell C2 is an end time next day I want cell D2 to give me total hours in digital format. I am using Apple Numbers v12.Any ideas please
iMac 27″, macOS 11.6
Hi TenpinBozz,
I see that you are faced with "crossing midnight" problem.
When you enter a time of day, Numbers automatically adds the Date to your entry.
If you enter start and end on the same day, (Row 2, below) we need to add 1 day ("1d") to the calculated hours.
It is best to enter the start time and the end time on the actual start and end days (Row 3).
This formula will cope with either scenario.
I have formatted columns B and C to display both Date & Time for clarity. There is no need for you to do this.
Formula in D2 (and fill down):
DUR2HOURS(IF(C2<B2,C2−B2+"1d",C2−B2))
If end is less than start, then add 1 day, else simply subtract the times of day.
DUR2HOURS converts the result to a number of hours.
Regards,
Ian.
Hi TenpinBozz,
I see that you are faced with "crossing midnight" problem.
When you enter a time of day, Numbers automatically adds the Date to your entry.
If you enter start and end on the same day, (Row 2, below) we need to add 1 day ("1d") to the calculated hours.
It is best to enter the start time and the end time on the actual start and end days (Row 3).
This formula will cope with either scenario.
I have formatted columns B and C to display both Date & Time for clarity. There is no need for you to do this.
Formula in D2 (and fill down):
DUR2HOURS(IF(C2<B2,C2−B2+"1d",C2−B2))
If end is less than start, then add 1 day, else simply subtract the times of day.
DUR2HOURS converts the result to a number of hours.
Regards,
Ian.
My problem appears to be solved.
Many Thanks
Formula to calculate Time
