time and cost calculations

[chocolatelustbo]

This is probably easy when you know how...

I have an Excel XP cost-analysis spreadsheet with columns that show:

task
role allocated to task (i.e. job title - which is a 'pick list' using
the Data Validation)
person allocated to task (a LOOKUP based on the role field)
pay rate (a LOOKUP based on the role field)
time to perform task (in the format of hh:mm)
regularity of task per month (based on 4.33 weeks / month - from 52
weeks / year divided by 12 months)
time to perform task / month (calculated from 'time to perform task' x
'regularity of task per month')
monthly cost of task ('pay rate' x 'time to perform task / month)

The 'monthly cost of task' field does not display correctly - it
defaults to dd:mm format. When I change this to number or accounting
the results just don't calculate correctly.

Easy answer would probably be to convert the 'time to perform task'
field to decimal format but it would be nicer (and easier for data
input) to be able to enter the time in hh:mm format.

Anybody out there able to help? Quite happy to send the spreadsheet if
that helps.

cheers.

 

[Ron Rosenfeld]

This is probably easy when you know how...

I have an Excel XP cost-analysis spreadsheet with columns that show:

task
role allocated to task (i.e. job title - which is a 'pick list' using
the Data Validation)
person allocated to task (a LOOKUP based on the role field)
pay rate (a LOOKUP based on the role field)
time to perform task (in the format of hh:mm)
regularity of task per month (based on 4.33 weeks / month - from 52
weeks / year divided by 12 months)
time to perform task / month (calculated from 'time to perform task' x
'regularity of task per month')
monthly cost of task ('pay rate' x 'time to perform task / month)

The 'monthly cost of task' field does not display correctly - it
defaults to dd:mm format. When I change this to number or accounting
the results just don't calculate correctly.

Easy answer would probably be to convert the 'time to perform task'
field to decimal format but it would be nicer (and easier for data
input) to be able to enter the time in hh:mm format.

Anybody out there able to help? Quite happy to send the spreadsheet if
that helps.

If I understand you correctly, you can still enter the time in hh:mm format.
But in the formulas in which you use "time", multiply that number by 24 in
order to convert to decimal format.

For example

Time In Time Out Pay Rate / hr
6:00 14:00 $12.50

Amt Paid would be (TimeOut - TimeIn) * 24 * PayRate


--ron