Excel time sheet formula

  Bee Jay S 18:51 03 Sep 2005
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 2005

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 2005

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 2005

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

TonyV

  Simsy 19:40 03 Sep 2005

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,

Regards,

Simsy

  VoG II 19:42 03 Sep 2005

This click here is even better IMHO.

  Noldi 19:43 03 Sep 2005

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.
Noldi

  Simsy 06:24 04 Sep 2005

VoG™

Regards,

Simsy

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

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Inside the iMac Pro - Apple's most powerful Mac yet

iMac Pro release date, UK price & specs

Comment nettoyer Windows et optimiser son PC gratuitement ?