I am trying to create a timesheet to work out weekly flexi time.

R

Ranger Nige

I have downloaded a template (Weekly timesheet with breaks) from Excel to
work out my flexi-time. However I am having difficuties in getting answers
in hours and minutes i.e.
Morning 08:30 to 12:30
Lunch 12:30 to 13:24
Afternoon 13:24 to 16:30
should equate to 7 hours 9minutes (Standard working day)
5 days at these hours = 35 hours 45minutes (Standard working week)
if I work more or less than these times I want to be able to see individual
hours & minutes plus being able to carry forward from one week to the next?
 
J

JLatham

In the cells where you do your hours calculation, use Custom formatting and
format them as
[h]:mm:ss
Then when you subtract a starting time from ending time you'll get
hours/minutes elapsed. That is:
12:30 - 8:30 will appear as 4:00:00
13:24 - 12:30 will appear as 0:54:00
16:30 - 13:24 will appear as 3:06:00
and if you add the first result to the last (with same formatting), you'll
get 7:06:00, i.e. 7 hours 6 minutes.

Not sure how to tell you to carry over hours to the next week, since I don't
know whether you can "owe" them time also. But if you'll put your standard
work week into a cell somewhere (again with that special format) as 35:45:00
then you can see any time that may be carried over:
With the standard workweek in cell F5 (just for example, could be anywhere)
then to calculate any time above 35hrs 45 minutes to carry forward, if Total
hours worked for the week is in E6, then this formula should show you time to
carry forward:
=MAX(E6-F5,0)
Here's a formula that would show if you owe hours, but since Excel abhores
negative times, we can't show a -2 hours or anything like that. Might have
to deal with that in some other way.
=IF(E6<F5,"owe them " & ROUND((F5-E6)*24,2) & " Hours",MAX(E6-F5,0))
 
H

Hans Terkelsen

Ranger Nige said:
I have downloaded a template (Weekly timesheet with breaks) from Excel to
work out my flexi-time. However I am having difficuties in getting answers
in hours and minutes i.e.
Morning 08:30 to 12:30
Lunch 12:30 to 13:24
Afternoon 13:24 to 16:30
should equate to 7 hours 9minutes (Standard working day)
5 days at these hours = 35 hours 45minutes (Standard working week)
if I work more or less than these times I want to be able to see individual
hours & minutes plus being able to carry forward from one week to the next?

You might try this Custom format in the time cells.
[h]:mm;-[m] \min

The [] parentheses, which are used to display the full value,
solve the negative time problem in a one legged way.
Meaning that [h] can show negative hours and
[m] can show negative minutes.
But one can not get both.

Hans.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top