EXCEL Time Calculation

What formula is used to calculate the difference in time between two cells in order to make a timesheet for hours worked which is then multiplied by the hourly rate.

For example what formula would go in E2:

C2 = 9:00 AM

D2 = 3:30 PM

E2 = Minutes from C2 TO D2 (or Hours with fractions of hours to 2 decimal points)

 

Concept seems a bit askew here.
You need to have the dates in Julian form and the times in 24
hour form. There is no way to calculate the minutes between
two cells when one cell shows 11 : 59 PM and the other shows
2 : 45 PM the next day. Manipulating and calculating with dates
and times is one of the most difficult programming tasks.
Good luck !
regards

 

solanabch,

I have an Excel sheet that I use to check hours in the method you are suggesting.

In Excel If you format all your cells containing times in the 00:00 time format you can add and subtract them. Keep in mind that when calculating the answers are in hh:mm (hour and minutes) and not hh.hh (hours in decimal form)

You can change a time formatted cell to a decimal time reference. I find it best to place the result in a new cell. So lets say in cell D4 you type B4*24. This will convert the time shown in cell B4 (lets imagine it to be 23 hours 30 minutes) into 23.5 hrs. This assumes that cells B4 and D4 are both formatted as time.

You can then multiply your decimal time by whatever hourly rate. Make sure that the cell showing the result of the hourXrate is formatted as a number - if this cell is formatted as time then then calculation does not display correctly.

If you are entering times from two different days - say clocked in at 10:15pm and left at 9:30 am, you would need to enter the times as 22:15 and 33:30. The times will show as 22:15 and 09:30, however you enter times past Midnight as the time +24 so that Excel calcuates the difference correctly.

Not sure if I explained that quite as clear as I could have.

You can download my sheet from here. I have put some fictional times in there so you can see how the formulas work.

Cheers,

ICE