Im trying to show downtimes at a plant. 09:00 - 12:00 - downtime of 3 hours. what formula do i use to do this? when i subtract the "hours" then the cell that contains the formula is in "hours" and doesn't covert to "number" correctly. Also, when the total hours goes over 24 its converting it into days, but i want total hours. Any help would be appreciated.
Convert timeclock to number value.
- Thread starter Carol
- Start date
NoS
Member
- Joined
- Jan 17, 2013
- Messages
- 835
- Reaction score
- 0
- Points
- 16
- Location
- British Columbia
- Excel Version(s)
- Excel 2010
Using 24 hour time, if the beginning time is in A2 and the ending time is in B2 try this in C2
=ABS(($B2) - ($A2) - (($A2) > ($B2))) * 24
=ABS(($B2) - ($A2) - (($A2) > ($B2))) * 24
- Joined
- Mar 21, 2011
- Messages
- 1,960
- Reaction score
- 0
- Points
- 36
- Excel Version(s)
- O365
Multiply by 24
=(B2-A2)*24
be sure to format it as General, or N it
=N((B2-A2)*24)
=(B2-A2)*24
be sure to format it as General, or N it
=N((B2-A2)*24)
NoS
Member
- Joined
- Jan 17, 2013
- Messages
- 835
- Reaction score
- 0
- Points
- 16
- Location
- British Columbia
- Excel Version(s)
- Excel 2010
Carol
Correction to the formula I posted above, should be
=ABS(($B2) - ($A2) + (($A2) > ($B2))) * 24
also, you say
Correction to the formula I posted above, should be
=ABS(($B2) - ($A2) + (($A2) > ($B2))) * 24
also, you say
how does it know it's over 24 hours? Is there something else that would indicate the date the times are for?
Pecoflyer
Admin Alumnus
- Joined
- Oct 13, 2011
- Messages
- 1,802
- Reaction score
- 1
- Points
- 38
- Location
- Brussels Belgium
- Excel Version(s)
- 2010 on Xubuntu - O365
Format as [h]:mm:ss
Mike_Alex
New member
You may also want to try the convert formula
http://office.microsoft.com/en-us/excel-help/convert-times-HP003056110.aspx
http://office.microsoft.com/en-us/excel-help/convert-times-HP003056110.aspx