J
JR21
In one cell I have 8:00-5:00 how do I get the cell under it to add these
hours for me so I don't have to manually put in 9 hours?
hours for me so I don't have to manually put in 9 hours?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Pimamedic
Two columns
Start Time Column A
End Time Column B
Format both for time
=(b1-A1)
Start Time Column A
End Time Column B
Format both for time
=(b1-A1)
A
Ashish Mathur
Hi,
Try this. The assumption herein is that if the ending time is less than the
beginning time, then the days are apart by a day
=IF(1*MID(B5,SEARCH("-",B5)+1,4)<1*LEFT(B5,SEARCH("-",B5)-1),1*MID(B5,SEARCH("-",B5)+1,4)+TIME(12,0,0)-1*LEFT(B5,SEARCH("-",B5)-1),1*MID(B5,SEARCH("-",B5)+1,4)-1*LEFT(B5,SEARCH("-",B5)-1))*24
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
Try this. The assumption herein is that if the ending time is less than the
beginning time, then the days are apart by a day
=IF(1*MID(B5,SEARCH("-",B5)+1,4)<1*LEFT(B5,SEARCH("-",B5)-1),1*MID(B5,SEARCH("-",B5)+1,4)+TIME(12,0,0)-1*LEFT(B5,SEARCH("-",B5)-1),1*MID(B5,SEARCH("-",B5)+1,4)-1*LEFT(B5,SEARCH("-",B5)-1))*24
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
T
T. Valko
It would be much easier if you used separate cells for the start and end
time *and* you either use a 24 hr format or include the AM/PM with a 12 hr
format.
A1 = 8:00 AM (or 8:00 in 24 hr format)
B1 = 5:00 PM (or 17:00 in 24 hr format)
For a result in time format:
=MOD(B1-A1,1)
Format as h:mm
For a result in decimal format:
=MOD(B1-A1,1)*24
Format as General or Number
time *and* you either use a 24 hr format or include the AM/PM with a 12 hr
format.
A1 = 8:00 AM (or 8:00 in 24 hr format)
B1 = 5:00 PM (or 17:00 in 24 hr format)
For a result in time format:
=MOD(B1-A1,1)
Format as h:mm
For a result in decimal format:
=MOD(B1-A1,1)*24
Format as General or Number