Logical test formula not updating when date is changed

**MLBarie** · 08-15-2012, 03:37 PM

I was given someone else's worksheet (who no longer works here) to update for the coming 2013 year. The purpose of the formula in question is to automatically calculate FTE based on start and end date for each month. When I changed the date ranges from 1/1/2012 to 1/1/2013; and end date 12/31/2012 to 12/31/2013, the formula returns the equivelent of 0 instead of 1. If I leave the start range blank, it will calculate 1.

Here is the formula for January:

=IF($J3<=DATE(YEAR(M$1),MONTH(M$1),1),IF($K3>=M$1,1,IF($K3<DATE(YEAR(M$1),MONTH(M$1),1),0,NETWORKDAYS(DATE(YEAR(M$1),MONTH(M$1),1),$K3)/NETWORKDAYS(DATE(YEAR(M$1),MONTH(M$1),1),DATE(YEAR(M$1),MONTH(N$1),0)))),IF($J3<=M$1,NETWORKDAYS($J3,M$1)/NETWORKDAYS(DATE(YEAR(M$1),MONTH(M$1),1),DATE(YEAR(N$1),MONTH(N$1),0)),0))

Any help would be much appreciated.
Thanks,
Maggie

**Bob Phillips** · 08-15-2012, 03:48 PM

Can you post the workbook, it is meaningless without the data.

**CheshireCat** · 08-15-2012, 03:48 PM

Hi Maggie,

There's nothing in the formula to indicate that it would be sensitive to your changing the date ranges. What data do you have in cells: J3, K3, M1, and N1? Did you change the dates in each of these cells?

**MLBarie** · 08-15-2012, 03:58 PM

I can't post the workbook b/c of the sensitivity of where I work.

J3 is start date
k3 is end date
m1 is january
n1 is february

**daddylonglegs** · 08-16-2012, 10:52 AM

Originally Posted by MLBarie

J3 is start date
k3 is end date
m1 is january
n1 is february

Hello Maggie

Judging by your formula M1 and N1 (and other dates, presumably you have one for each month of the year) are the last day of the month, i.e. 31st Jan 2012 and 29th Feb 2012. They may be formatted to show just the month name but if you want the formula to work for 2013 you need to update those dates so that they are 31st Jan 2013, 28th feb 2013 etc.

You can make any change easier by having a single cell with the year, e.g. in J1 put the year, e.g. 2013

Then in M1 use this formula to generate the last day of Jan for that year

=DATE(J1,1,31)

and in N1 copied across use this formula

=EOMONTH(M1,1)

Now if you change the year in J1 all the months will change automatically as required

....also I think you can use this formula to simplify the FTE calculation

=MAX(0,NETWORKDAYS(MAX($J3,M$1-DAY(M$1)+1),MIN($K3,M$1))/NETWORKDAYS(M$1-DAY(M$1)+1,M$1))