Excel Logic Design

[jrobin5881]

I have a spreadsheet that is designed to determine monthly cost savings which roll into an annualized amount.

Column F has a date that the end user puts in for when the savings is "expected" to begin. The potential date range used is the fiscal year which is Oct 1, 2008 and ends Sept 31, 2009. In row 8, starting at column "I" I have the column headings begining with Oct-08, then Nov-08, then Dec-08 etc etc. In row 6 above each of the month headings I hid the number of work days for the particular month. So, I6 has 26, J6 has 24 etc etc.

Here's my issue. First I want to add 30 days to the date that my user has input as the expected begining date of savings. Secondly, if the date falls on any date after the first of the month i need to credit them with the remaining work days and not the full month. For instance,my user inputs 11/11/08 in cell F11. What I want to happen is add 30 days to the user input date which makes the date 12/11/08. In cell K11 under the Dec-08 heading (and where the formula below resides)I want to multiply the remaining 16 work days (6 day workweeks - xmas holiday) to determine the expected savings for Decemeber.

If the date were later then December I want to credit the unit with the full amount of work days. So from January to September it will multiply by the full amount of work days in row 6

If the expected savings begin date were to be December I want a zero to appear under the Oct, Nov, and Dec column headings.

Any ideas? or am i biting off too much?

=IF(F11<=K8,K$6*($V11*8),0)

FYI- V11 is a ratio factor being applied to savings.

 

[SkipVought]

How about posting some sample data. Please use TGML Tag TT(search this page for TGML to find out how to use) and make sure that you line up your data in columns that is intelligible to view.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Take a look at this.
[tt]
=IF(AND($F11+30>=K$8,$F11+30<L$8),K$6*NETWORKDAYS($F11,K$8)/K$6*($V$11*8),IF($F11+30<K$8,K$6*($V$11*8),0))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jrobin5881]

Skip,

thanks. I was just looking up the If and functionality. I put your suggestion under the Dec-O8 column in cell K11 and have the date 12/1/2008 in cell F11 and I get a #NAME? error as the result.

 

[AnotherHiggins]

Go to Tools > Addins and check the box beside Analysis TookPak.

That addin must be installed in order to use NetWorkdays.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jrobin5881]

anotherhiggins thanks now I"m getting a number but it's not correct.

Cell V11 has a value of 3.8, CEll K6 has 25 which is the total work days for the month. when I put the formula in cell K11 (under the Dec heading) and I have a date of 12/11/08 in cell F11 it's returning a value of 30.4 which mathmatically the result of multiplying 3.8 * 8. It's not picking up the actual days of the month to multiply by. The formula should do this when 12/11/08 is in cell F11. 17*(3.8*8) = 516.80 The number 17 represents the remaining work days in December multiplied by the factor which is multiplied by 8

 

[jrobin5881]

Wow- It's working excellent!!! For some reason the number formatting was off. It adjusted fine when I used format painter. There is one small hitch. If I enter a user date of 3/2/09 and the calculation adds 30 days then I need the column for March of 09 to have a zero in it. The savings will actually accrue from April 2 of 09.

Do I need to add another factor to the if statement?

 

[SkipVought]

Words do mean something. Date + 30 is a very INEXACT calculation. Make your date then and then reference this cell rather than F11+30...
[tt]
=DATE(YEAR($F11),MONTH($F11)+1,DAY($F11))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jrobin5881]

I"m getting an error message when I try and change the formula


=IF(AND(DATE(YEAR($F11),MONTH($F11)+1,DAY($F11))>=M$8,DATE(YEAR($F11),MONTH($F11)+1,DAY($F11)<N$8),M$6*NETWORKDAYS($F11,M$8)/M$6*($U$11*8),IF(DATE(YEAR($F11),MONTH($F11)+1,DAY($F11)<M$8,M$6*($U$11*8),0))

 

[AnotherHiggins]

3/2 + 30 = 4/1, not 4/2.

Do you really want to add 30 days, or add one month and keep the same day of the month?

What do you want to happen if someone enters 1/31? Adding 30 days would skip February and return 3/2 (3/1 for leap years). Is that what you want, or do you want to return return 2/28?

If you want to add a month, you can use EDate now that you have Analysis ToolPak installed.
[tab]=EDate(A1, 1)
will add one month to the value in A1.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

missing )

also change the date ref in networkdays...
[tt]
=IF(AND(DATE(YEAR($F11),MONTH($F11)+1,DAY($F11))>=M$8,DATE(YEAR($F11),MONTH($F11)+1,DAY($F11)[red])[/red]<N$8),M$6*NETWORKDAYS(DATE(YEAR($F11),MONTH($F11)+1,DAY($F11)),M$8)/M$6*($U$11*8),IF(DATE(YEAR($F11),MONTH($F11)+1,DAY($F11)[red])[/red]<M$8,M$6*($U$11*8),0))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jrobin5881]

The math is not working correctly.

If this section =IF(AND(DATE(YEAR($F11),MONTH($F11)+1,DAY($F11))>=M$8,DATE(YEAR($F11),MONTH($F11)+1,DAY($F11))<N$8 evaluates to true then I want to take the remaining work days in the month and multiply it by the factor in another column and then by 8 hours.

If Im reading this correctly it appears that this section M$6*NETWORKDAYS(DATE(YEAR($F11),MONTH($F11)+1,DAY($F11)),M$8)/M$6*($U$11*8)[\b] is taking the total workdays for the particular month that I hardcoded in M6 and multilplying it by the Networkdays and then dividing by total workdays for the particular month that I hardcoded in M6 and multiplying by the factor column then by 8.

It should be something like this. If the date plus 30 were to be April 25th then there would be 5 days left in the month - 4/25 Fri, 4/26 Sat, 4/28 Mon, 4/29 Tue, and 4/30 Wed
The formula should do this 5 * (3.8 * 8) 3.8 is the factor in column U and 8 represents 8 hours and the 5 is the remaining days in the month. The result should be 152 hours for the month of April. I'm getting 182 using the current formula

 

[AnotherHiggins]

If you answered this, I missed it:

What do you want to happen if someone enters 1/31? Adding 30 days would skip February and return 3/2 (3/1 for leap years). Is that what you want, or do you want to return return 2/28?



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jrobin5881]

Sorry - I would need 2/28. So I should replace (DATE(YEAR($F11),MONTH($F11)+1,DAY($F11)[\b] portions of the formula with =EDate(A1, 1)?

I found that my math is wrong because of the absolute references so I"m in the process of editing all of them

 

[AnotherHiggins]

yes, I'd make the substitution.

A perk is that it will be easier to read because the whole formula will be shorter.

FYI: A shortcut to change Relative/Absolute references is to select a range within your formula, then repeated press [F4].

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.