Time formulas in Excel

I

Izzy

aCan anyone help please?

I am trying to put together a spreadsheet that will keep track of my hours
worked in a month, on different days. I have 2 rates at work. On day shift
rate, and a night shift rate. sometimes I work 09:00 till 18:00 at rate one,
and sometimes 22:00 till 06:00 at rate 2. The days vary, and I can get it to
work my day shift, but not my night shift. The calculation cell needs to be
able to calculate the difference in both shifts. Thanks for the help :)
 
V

VBA Noob

Say start time is in A1 and End time in B1 then enter the below formula
into C2 and format as [h]:mm

=IF(A1>B1,B1+1-A1,B1-A1)

VBA Noob
 
O

oldchippy

Izzy said:
aCan anyone help please?

I am trying to put together a spreadsheet that will keep track of my
hours
worked in a month, on different days. I have 2 rates at work. On day
shift
rate, and a night shift rate. sometimes I work 09:00 till 18:00 at
rate one,
and sometimes 22:00 till 06:00 at rate 2. The days vary, and I can get
it to
work my day shift, but not my night shift. The calculation cell needs
to be
able to calculate the difference in both shifts. Thanks for the help
:)
Hi Izzy,

If your day rate was say, £15.25
and your night rate was £20.25

you could try this

=IF(A1>B1,B1+1-A1,B1-A1)*24*15.25
=IF(A2>B2,B2+1-A2,B2-A2)*24*20.25

Then format C1 to currency

oldchippy :)
 
D

daddylonglegs

To calculate time difference in hours either

=B1-A1+(A1>B1)

or

=MOD(B1-A1,1)

if you want the hours in decimal format multiply by 24, e.g.

=MOD(B1-A1,1)*24
 
I

Izzy

Thanks for that guys. Would you know the following too......

We have 2 pay rates. 06:00 to 22:00 is rate 1 and 22:00 to 06:00 is rate 2.

I can work out the rates if I work within rate 1 or rate 2, but if I work
03:00 to 15:00 or 20:00 to 04:00 crossing over between the rates, I get hung
up. I need to be able to work out my days pay for all my shifts including the
cross overs.

Thanks :)
 
D

daddylonglegs

Not particularly straightforward but if your shift start time is in A
and end time in B2, in time format then this formula in C2 will giv
you rate 2 hours

=IF(MOD(A2+1/12,1)<1/3,MIN(1/3,MOD(B2+1/12,1))-MOD(A2+1/12,1),IF(MOD(B2+1/12,1)<A2,MIN(MOD(B2+1/12,1),1/3),0))

format as h:mm

this format in D2 will then give you rate 1 hours

=MOD(B2-A2,1)-C2

then to calculate total pay for shift

=(C2*rate2+D2*rate1)*2
 

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