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

[Guest]

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?

 

[Guest]

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))

 

[Hans Terkelsen]

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.