Forum Discussion
Solved
Time conversion
Hello,
I need some help with converting time.
I have a spreadsheet with start time, finish time and lunch break. An example is
07.00AM - 03.06PM less 0.30 lunch is a 7:36 hour day. I need a formula for the 7.36 to have the minutes only to a decimal so the result is 7.6. I just have the basic formula (Cell A-Cell B) - Cell 2 .
Any help greatly appreciated. I have the full minutes to decimal converter in excel already if that helps. I figure I could do a X Lookup on that but not sure about the formula structure.
Thanks
Try this:
=((B4-A4)-C4)*24
You will need to format the result (in D4 above) as a number rather than as a time. But that math works just fine.
The "trick" if it can be called that, is that minutes are stored as decimal fractions of the 24 hours. That's why the number that results from the figures in the parentheses needs to be multiplied by 24.
Try this:
=((B4-A4)-C4)*24
You will need to format the result (in D4 above) as a number rather than as a time. But that math works just fine.
The "trick" if it can be called that, is that minutes are stored as decimal fractions of the 24 hours. That's why the number that results from the figures in the parentheses needs to be multiplied by 24.
mathetes Thanks so very much for your prompt reply and answer. Its now working as I need it to.
You're a gem.
