Excel time sheet formula

  Bee Jay S 18:51 03 Sep 05

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 19:21 03 Sep 05

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


  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.

click here is an excellent source of advice on using Excel for time calculations.

Good luck,



  VoG II 19:42 03 Sep 05

This click here is even better IMHO.

  Noldi 19:43 03 Sep 05

You can down load ready made excel sheets from the microsoft office webb site. have a look there maybe a ready made version of such a sheet.

  Simsy 06:24 04 Sep 05




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…