calculate payment due based on hours worked

[diverdave]

I have the following:

Column A=date (format is: Date 3/14/01)
Column B = start time (format is: "Custom h:mm")
Column C = end time (format is: "Custom h:mm")
Column D = total hours (format is: "Custom h:mm")
Column E = wages due (format is: "Currency ($1,234.10) )

In column E I want to return the value in dollars for wages due by multiplying the total hours worked in column D by an hourly wage
i.e. 3.5 hours X $20.00 per hour = $70.00 wages due.

Date = 1/1/05 / Start time = 8:00 / End time = 16:00 / Total Hours = 8.00 / Total $ due = $160.00

I would appreciate help with the formula to accomplish this.

Thanks, Dave

 

[tactps]

Assuming that D3 contains the total hours:

=+D3*24*20

 

[diverdave]

Great!

Thanks pactps - that works perfectly.

I can't quite grasp the logic of how the conversion is being done though. If you care to take the time to explain it I'd appreciate it.

Either way, thanks for the help.
Dave

 

[bobmc]

You may want to incorporate another column ("wage") in column E, moving "wages due" to column F, then use formulas as follows:

in D1, (Hrs Worked) =IF(C1<B1,(C1-B1)*24+24,(C1-B1)*24)
in F1, (Wages Due) =D1*E1

Formatting of cells is as follows...

A1 - Date
B1 - Time (1:30 PM)
C1 - Time (1:30 PM)
D1 & E1 - General
F1 - Currency

When entering times into B1 and/or C1, you must enter them as "15:00" for 3 pm, "9:00" for 9 am, "23:00" for 11 pm, etc

 

[tactps]

The Excel help contains a good explanation, but this could help:

Times in Excel are really numbers between 0 and 1.

To convert to hours, multiply by 24
Minutes - multiply by 1440 (ie 60 * 24)
Seconds - multiply byb 86400 (60 * 60 * 24)

 

[diverdave]

tactps- thanks

Got it - Thanks,
Dave