Conditional Formula

[mctighe]

I am trying to put together a conditional formula which will return a value if the condition met is the first WORKDAY of the beginning of the month. If the condition is not met the value will return a "$0".

For example if the date is the condition and A1 is 3/1/2015 (Sun), B1 is 3/2/2015 (Mon), and C1 is 3/3/2015 (Tues) and the value I'm trying to return if the condition is met is $10,000 (which is in D5 ), the formula (in A5, B5, and C5) should return $0, $10,000, and $0 in the three respective cells.

I've tried creating formulas around the =WORKDAY(EOMONTH(A1,-1),1) command but I can't get anything to work properly. Can someone please help me with how to do this? Thanks so much!

[protonLeah]

Please Login or Register  to view this content.

[daddylonglegs]

=if(and(weekday(a1)=2,day(a1)<=7),10000,0)

That works if the first workday of the month is a Monday.....but not in other cases.

I think your WORKDAY formula is on the right track, try this formula in A5 copied across

=IF(A1=WORKDAY(EOMONTH(A1,-1),1),$D$5,0)

[mctighe]

I received this formula which seems to work:

=(A1=WORKDAY(EOMONTH(A1,-1),1))*$D5

In a similar vein, does anyone know how to create a formula which returns a value based on the condition that it is the 18th of the month with the caveat that the 18th is a workday. If the 18th is not a workday, the formula should return the value corresponding with the last workday before the 18th. (e.g. on Friday if the 18th is a weekend or the day before a holiday if the 18th is a holiday). All other cells that don't meet the condition should return "$0".

In other words, I am compiling a budget forecast with a fixed expense that hits every month on the 18th, and I need a formula which will return the appropriate value on that day or on the latest previous workday if the 18th is a weekend or holiday. Thanks!

[daddylonglegs]

.....based on the condition that it is the 18th of the month with the caveat that the 18th is a workday. If the 18th is not a workday, the formula should return the value corresponding with the last workday before the 18th. (e.g. on Friday if the 18th is a weekend or the day before a holiday if the 18th is a holiday)..

That's effectively the same as allocating the value to the first working day before the 19th so this formula would do that

=(A1=WORKDAY(A1-DAY(A1)+19,-1))*$D5

If you want to consider holidays you need to add a holiday range in to the WORKDAY function