Formula Help

[b_wint16]

Ok. So I am super amateur status regarding excel and excel formulas. However, I am trying to do something somewhat complicated.

I am trying to design a formula that works in two parts. The context of this formula is that I am using this for billing purposes. I have a column that contains a start and end date with a total number of hours worked between those two dates. What I am trying to do bill (multiply) certain hours that were before the year 2017 by a certain rate and bill the hours worked after the year 2017 by a new rate.

My formula I used was IF(AND(E6>=DATE(2017, 1, 1),F6>=DATE(2017, 1, 1)),L6*M4,L6*M3)

The issue with this formula is that it does not take into account if the start date(E6) was in year 2016 but he end date(F6) was in 2017. If that was the case I would need to bill a certain percentage of the total hours worked in 2016 by one rate and the remainder by the new rate.

Any suggestions. I am not sure if I even explained this correctly. However, I am stumped and would love some suggestions

 

[godsaaint]

Could you provide an example of your data?

 

[b_wint16]

Could you provide an example of your data?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Work Days[/TD]
[TD]Total Hours[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Planning[/TD]
[TD]12/18/2016 (E6)[/TD]
[TD]01/13/2017[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]$1620[/TD]
[/TR]
[TR]
[TD]Environment[/TD]
[TD]12/19/2016[/TD]
[TD]01/25/2017[/TD]
[TD]28[/TD]
[TD]45[/TD]
[TD]$2503[/TD]
[/TR]
</tbody>[/TABLE]

This is a small subset of what my spreadsheet looks like. I am trying to figure out a way to use calculate the percentage of hours worked from 2016 to 2017 so that I can multiply that total by the old rate of $55.

I hope this helps clarify some.

 

[ChuckDrago]

b_wint... I suggest you start by designing a way to accumulate hours splitted as you need them. Nothing in the numbers tells whether the hours are worked exactly prorated. Notice that 30 hours divide into 20 days as 1.5 hrs/day, whereas 28 into 45 results in 1.6 hrs/day. The dates cannot play a role either, because you do not tell what days were worked within each year. In my humble opinion, you have to re-design your data set to make it workable. Sorry.

 

[godsaaint]

I agree with ChuckDrago, I think it'd would be better to work around it, something like this:

Insert 2 new helper columns, so you have work days and total hours respectively for each year.
[TABLE="width: 747"]
<tbody>[TR]
[TD]Project
[/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]Work Days 2016
[/TD]
[TD]Total Hours 2016
[/TD]
[TD]Work Days 2017
[/TD]
[TD]Total Hours 2017
[/TD]
[/TR]
[TR]
[TD]Planning
[/TD]
[TD="align: right"]18/12/2016
[/TD]
[TD="align: right"]13/01/2017
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]15
[/TD]
[/TR]
[TR]
[TD]Environment
[/TD]
[TD="align: right"]19/12/2016
[/TD]
[TD="align: right"]25/01/2017
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]16??
[/TD]
[TD="align: right"]18
[/TD]
[TD="align: right"]29??
[/TD]
[/TR]
</tbody>[/TABLE]

You can use the functions =NETWORKDAYS(E6,"31/12/2016") and =NETWORKDAYS("01/01/2017",F6) for the work days. For the hours, as ChuckDrago mentioned there is not apparent logic in your example so you'll know what to do.

After, you can use a simple formula like =(TotalHours2016*rate2016)+(Totalhours2017*rate2017)

 

[b_wint16]

Thank you both! That sounds like a great suggestion. I think a redesign would make this a much smoother process.