Calculate Work Days Based on Hours Worked

**FM1** · 02-04-2009, 08:14 AM

Hey everyone. I am working on a resource model where I need to calculate cost variance based on actuals versus forecasted cost. The issue I have is that the resources are paid on a day rate and the spreadsheet is based on hours.

For example:

[Col A - Name] "John Smith" [Col B - Daily Rate] "£200" [Col C - Forecasted Hours] "15.0" [Col D - Actual Hours] "23.0".

Now in columns E and F I need to work out both forecasted cost and actual cost based on that information. The parameters are that each work day is based on 7.5 hours and there is no overtime taken into consideration. So even if someone works 10 hours, they will be paid only for the equivalent of one day's work (7.5 hours). If someone has worked 15 hours then its 2 days and so on.

**Back2Basics** · 02-04-2009, 08:21 AM

I have added a column that calculates the hourly rate, and then maade the calculations accordingly.

Is this what you are looking for?

**FM1** · 02-04-2009, 08:31 AM

Originally Posted by Back2Basics

I have added a column that calculates the hourly rate, and then maade the calculations accordingly.

Is this what you are looking for?

Unfortunately not. The issue with that is that the calculations assumes that the resources are paid on an hourly rate which they arent. All resources are paid a daily rate regardless of the hours they have worked. So if someone gets paid £200 pounds a day (based on a 7.5 hour day) and works 10 hours for the entire day, they will only get paid the £200.

**DonkeyOte** · 02-04-2009, 08:37 AM

Re: normalised / non-normalised

You can't achieve what you want without breaking out the hours by day.... it's impossible to take say 37.5 hours and determine as to whether or not that's 5 days or 4 days + OT.

**FM1** · 02-04-2009, 08:43 AM

Originally Posted by DonkeyOte

Re: normalised / non-normalised

You can't achieve what you want without breaking out the hours by day.... it's impossible to take say 37.5 hours and determine as to whether or not that's 5 days or 4 days + OT.

Good point. Im going to have to think of another way around this.

Mods - feel free to close this thread as what Im wanting to achieve is not really possible.

Thanks.

**Back2Basics** · 02-04-2009, 10:25 AM

Is this something like what you are looking for?

I have made two columns for calculating the number of days forcasted and actual days worked. The costs are then generated using these values.

I have added some conditional formatting so that when the actual costs are greater than the forcast costs the text is red.

Is this the kind of thing that you are expecting to acheive?

**DonkeyOte** · 02-04-2009, 11:07 AM

B2B, per the OP there is no way to provide a solution based on the sample data given there is no way to calculate the actual costs based on the fact that the organisation is working on a non-normalised basis.

So if you use the example of 37.5 hours -- without knowing how those hours are broken down at a daily level it is impossible to compute the cost to the employer... for ex. the cost could be as simple as 5 days (each of 7.5 hrs), however, it's equally possible that the employee in question worked 10 hours Mon-Wed and 7.5 hrs on Thursday... therefore cost to employer on a non-normalised basis is 4 days (10 + 10 + 10 + 7.5) .. the OT worked Mon-Wed is irrelevant in calculating cost. Thus without breaking out the work by day it is impossible to calculate actual costs... forecast costs could be calculated if you assume 7.5 hr day constant but not actuals.

**Back2Basics** · 02-04-2009, 12:27 PM

I know what you are saying DonkeyOte, it is impossible to know who the hours are split up over the agreed working days - however I would have thought that it 37.5 hours are scheduled and 37.5 hours are worked, it would be unreasonable to only pay the employee for 4 days work, if 5 are forecast.

There are other obvious flaws in the spreadsheet that i have suggested - I just wanted to offer a solution for the time being, until FM1 has figured out how he is going to fix the issue.