Excel time query

OCdude · 15 Oct 2014 at 16:01

Simple decimal time question for any of you Excel fanatics out here

I have a client who enters times onto a timesheet in the format of:

0.5
-3.5
1
-1.5
3
0.75
4

(where the minus figures represent time owed/overtime).

When this is totalled, it comes to 4.25 (4h 15m) however the actual figure should be 3h 55m.

I confirmed that by changing the figures to times (e.g 0.75 = 00:45:00 etc)

Can i have this so that she can carry on entering times as decimals, but have the correct total displaying?!

Apologies if I’m doing something dumb!

Thanks!

Armageus · 15 Oct 2014 at 16:08

Summing it, 4.25 is the correct answer

9:15 total, less 5:00 owed = 4:15

*edit* not even sure how you would get 5 minutes using decimals as they aren't exactly easy to remember e.g. 5 minutes = .00347222

** edit edit ** 5 minutes actually 0.08333333, above I was thinking of date/time as julian numbers

zola25 · 15 Oct 2014 at 16:16

Why not change each field to HH:MM format?

skyripper · 15 Oct 2014 at 16:25

So they want to enter values as units of Hours.
(90 mins = 1.5)

Multiply each cell up by 60.
Total that.
Divide by 60.