Adding decimal minutes to regular minutes and hours (calc)

users
#1

Hi,

I have a project where I am trying to calculate the total amount of work hours for a project based upon the timestamp of calls throughout the day. The time at which the calls is placed is in hh:mm:ss while the duration of the call is in decimal time. I need to take the last call of the day and add the decimal time to get an accurate shift time. Do I just put the hh:mm:ss time in the time function and add that to the decimal time?

Thanks.

#2

Spreadsheet dates are formatted numbers in unit "Days".
0.0 <==> 00:00
0.25 <==> 06:00 1/4 of a day
0.5 <==> 12:00 1/2 of a day
0.75 <==> 18:00 3/4 of a day
1.0 <==> 24:00 (with number format [HH]:MM)

There is no difference between the day fraction on the left and the
formatted number on the right. Both sides yield the exact same results
for all kinds of calculations and comparisons.
Multiply by 24 in order to convert days to hours and switch to some
decimal format when the result looks wrong.

#3

While I haven't done this in a spreadsheet, I used to on a calculator.
I did it by converting between decimal degrees and degrees, minutes &
seconds.

BTW, as an indication of how long ago I did this, I used this method to
add up the time for tracks on an LP to figure out how much time I'd need
on a cassette. :wink:

#4

You could have used a spreadsheet back in the 80ies and it would be the
exact same method. Either multiply the days by 24 to get hours or change
the number format. As a third method you may separate full days from day
fractions.

A1: =PI()
=int(A1) => 3 full days
=mod(A1;1) => 3:24 (about 1/7 of a day)

A1 formatted as HH:MM => 03:23 (time on day #3)
A1 formatted as [HH]:MM => 75:24 (showing full hours)
=A1*24 => 75.4 hours

#5

So if I'm getting this right, I would just do something like this:

        A B

1 Last Call: 5:58:16

2 Duration: 6.24

Total: : =B1 + B2 (and format as needed)

#6

6.24 is a decimal in unit "hours".

5:58:16 is a number in in unit "days" because all dates and times in all
spreadsheets are days. The unformatted value might be close to 0.25 because
6 hours are 1/4 of a day. REMOVE THE FORMAT AND SEE.

Convert your hours to days and add it to the other days.

=A1/24+B1 (hours in A1)
The format should be [HH]:MM:SS when the result may exceed one day.