Excel 2007 : Formula to calculate the hours worked and gross amount earned

What you seek, cannot be done accurately because you have not supplied complete information inside your sample file.

I.e. On row 6, the Hours/Workday is only specified for the period from 1/1/2012 to 1/7/2012. In the first date range within the second set, part of the date range occurs before 1/1/2012. The Hours/Workday is not specified in this period.

After you clear up this issue, it's a matter of projecting the data of the first set. In other words, the first table needs to be convert from date ranges to a table of single days with the proper info. Then the individual Hours/Workday can be applied to the date ranges of the second set.

Hello and thank you so much for your reply.

Yes what you have said is exactly the issue I have.

This calculation is based on a person starting work on 1/1/12 and onwards. What I am trying to do is caculate what they have worked/earned in the government fortnights (like social security). The government fortnightly periods are set and the job seeker has to submit what they have earned during the social security fortnightly periods. And these often don't coincide with the jobseeker weeks/fortnights. In the example on my worksheet, the job seeker commenced work on 1/1/12. We know what they earned from the time they started work, but we need to calculate how much they worked and earned in the social security fortnights. I usually do it manually but I am trying to find a way doing it via excel to make it quicker. So as you can see, the first social security fortnight begins on 27/12/11 to 9/1/12. So I know there is the period between 27/12/11 to 31/12/11 that they earned nothing as they didn't begin work until 1/1/12.

I hope there is a way to calculate this easily.

Thanks again,

Ana.

Hi,

I do need the fortnightly amounts unfortunately.

Thanks,

Ana