calculating time

[mats]

i have a problem when wanting to calculate time using 24 hour clock, i have
attached a simple example:

These are the columns & row 1:
A B C D
E
Start Finish Time of break Break length Hours
8:00 18:00 13:00 1.00 9.00
23:00 7:00 2:00 1.00 ERROR

I want to be able to use it for night time hours, the example that is there,
I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do
this?
The formula that i am using is as follows: Format of cells is 13:30
=IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1)

 

[Pete_UK]

Try this instead of your formula:

=IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1)

Hope this helps.

Pete

 

[Harald Staff]

Hi

Standard durationformula for work past midnight -or not- is
=B1-A1+(B1<A1)
-I think they deserve a paid break working hours like that ;-)

HTH. Best wishes harald

 

[mats]

Pete,

That worked perfectly, but i have another problem on it, The night hours
needs to be calculated using a different rate, so i thought that i could have
2 totals, splitting the hours into the night rate & day rate, how can i do
this?

 

[Pete_UK]

Which hours do the night rate apply to, and which hours would be
classed as day rate?

Pete

 

[mats]

Pete,

11pm to 7am are classed as the night rate.

tks

Mats

 

[Pete_UK]

Is anyone likely to work a combination of day and night hours at the
same time? For example, starting at 9pm and working through to 1am, or
starting 6am till 12 noon?

If not, then we could just use your start time column to determine a
"shift" - 1 for day, 2 for night, then use this to determine which rate
of pay should be applied to the hours.

Hope this helps.

Pete

 

[mats]

Pete,

I doubt it very much that any person would be working a combination of day &
night hours, so how would i do this?

Tks

 

[Pete_UK]

You will need an extra column to determine which Rate to use (assume
column F), and a formula something like this:

=IF(AND(A1>7/24,A1<23/24),"Day","Night")

i.e. if the start time is after 7:00am but before 11:00pm then set to
Day otherwise Night. It would be easier to return the actual rates/hour
rather than the text, so that in the next column you only need to
multiply the hours by the rate to get the pay. So, guessing the rates
at 5.25 units/hour and 6.4 units per hour, you would have in coluumn F:

=IF(AND(A1<7/24,A1<23/24),5.25,6.4)

and in the next column:

=E1*F1

where column E contains the hours from the previous formula. Obviously,
both formulae should be copied down, and you might like to format them
to show currency.

Hope this helps.

Pete

 

[Pete_UK]

Slight amendment:

=IF(AND(A1>=7/24,A1<23/24),5.25,6.4)

This will include 7:00am start time as being at day rate.

Pete