Excel time sheet formula

Bee Jay S 18:51 03 Sep 05
Locked

I have a spreadsheet which records the start time of a shift in one column and the finish time in another. I want a formula to calculate the amount of hours worked, then in the next column a formula to times the hours worked by the hourly rate giving the cost of the shift
Can anyone help. Thanks

VoG II 18:57 03 Sep 05

With start time in A1, finish time in B1,

in C1 =B1-A1

in D1 =C1*20*24

(where the 20 is the hourly rate).

Format D1 as Number.

If C1 does not format correctly, Format as Custom - [h]:mm

TonyV 19:04 03 Sep 05

Format the start cell as Time/1:30PM say, and the same for the shift close cell. So if Cell C1 is start and cell C2 is shift close, in cell C3, put the formula =IF(((C2-C1)*24)=0,"",((C2-C1)*24)) This gives you the Total hours worked. Then in cell C4 put the formula =IF(C3*\$G\$4=0,"",C3*\$G\$4)Where G4 is the hourly rate. I use this on a very simple time sheet and it works fine. If the hourly rate is the same, you can change the G4 reference to what ever your rate is. If there are various rates, you can set a number of cells up with those rates and refer to them instead of G4.

Hope this helps

TonyV

TonyV 19:21 03 Sep 05

Whilst you were posting your response, I was writing mine!!

TonyV

Simsy 19:40 03 Sep 05

It is important to enter the times with a colon to seperate the hours and minutes, i.e. you MUST have 12:15 if you want 15 past 12... using a full stop or comma wont do.

(This can be achieved using macros, but there are disadvantages)

Also, depending on your requirements, you may need to make allowances for a shift finishing the day after it starts, i.e. going past midnight.

Good luck,

Regards,

Simsy

VoG II 19:42 03 Sep 05

Noldi 19:43 03 Sep 05

Noldi

Simsy 06:24 04 Sep 05

VoGâ„˘

Regards,

Simsy

This thread is now locked and can not be replied to.

How to watch Surface PC live stream | Surface PC release date, price, spec rumours: All-in-one…

1995-2015: How technology has changed the world in 20 years

Best Photoshop video tutorials: 8 video tutorial websites for Photoshop

Apple's event invitations decoded: A look back at 16 of Apple's most cryptic invites | Clues in…