Dear All,
We've got a works time sheet to allow us to daily log our hours.
It's an excel programme and i don't think it's very user friendly as it calculates things in decimals not time.
i.e.
If I add the following times of 'Time in' at 7.15am and 'time out' at 15.30pm it gives the daily total as 8.15 hours, not 8.25 hours.

Any ideas on how to get the "Daily Hours" to calculate it as hours and percentages of ( 7.75 hrs, 8.25 hrs etc )????

Am at work so will only be able to pop back occaisionally!
cheers
Q

there's probably an easier way than this but…

to convert hour.mins (in cell A1) to decimals

=ROUNDDOWN(A1,0)+MOD(A1,2)/0.6

and to convert the other way, from decimals to hours.mins

=ROUNDDOWN(A1,0)&"."&MOD(A1,1)*60

For the actual cells, try, right click > format cells > time > choose the option that suits you best. This may pish around with your fomulae though so give us a shout if it does.

Hi all,
I've used chris82's option and formatted the cells to 'time' and '13.30' and then i can subtract timein/time out and get an answer in hours and mins, which is great.
The next two columns are now complaining!!!!
The next column "Weekly total" and "accumulative total" now won't work as they won't accumulate the total hours. the final one seems to only add up to 24 then start gain.
Any thoughts?

( I admit reggie's option will probably give me totals but mess up the first columns!)

I'd have hoped this formatting would have been sorted before we'd been given this!!!

any further thoughts peeps?
and thanks for looking!
cheers
Q

Hi all,
I've used chris82's option and formatted the cells to 'time' and '13.30' and then i can subtract timein/time out and get an answer in hours and mins, which is great.
The next two columns are now complaining!!!!
The next column "Weekly total" and "accumulative total" now won't work as they won't accumulate the total hours. the final one seems to only add up to 24 then start gain.
Any thoughts?

( I admit reggie's option will probably give me totals but mess up the first columns!)

I'd have hoped this formatting would have been sorted before we'd been given this!!!

any further thoughts peeps?
and thanks for looking!
cheers
Q

the thing with formatting is that it usually doesn't change the underlying value of the cell, just how it appears.

It's sometimes better to modify the actual value, then you can add stuff up in the right way.

Select the cell where you want the total:

right click > format cells > custom > scroll down the list and select [h]:mm:ss

This will total hours with no limit at the 24 hour period.

Hope that helps!

Timesheets are a pain! I used to have to do them manually, but then the boss wised up and now we use: http://www.dovico.com

The whole thing is now fairly painless.

chriss you're a star!!!!!
I've been messing about with the formatting like you and another friend said. I've now got the columns ( time in/out, daily total,weekly total and yearly total ) adding up properly working in hours and minutes.
The final column " hours left " I couldn't sort out, but tiredness and friday-itus were also kicking in.
Went home detemined to sort, and was day dreaming watching tell and thought " what about the custom > hrs mins option…."
….just like you've said!
can't wait for monday now ….well, p'raps not that keen!!!!!!!!

thanks for heading me in the right direction.

Speedy – i think I'm on the right track, but will look it up anyway.

thanks all.
Have a Jolly Good Weekend
cheers
Q