J JR21 Aug 10, 2009 #1 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?
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?
P Pimamedic Aug 11, 2009 #2 Two columns Start Time Column A End Time Column B Format both for time =(b1-A1)
A Ashish Mathur Aug 11, 2009 #3 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
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
T T. Valko Aug 11, 2009 #4 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
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
