WORKDAY formula round up (perhaps arrays)

[Domyzon]

Hello!

I have a problem I am trying to solve.

Problem 1.

1. I have daily dates from 01.01.2017 - 31.12.2017 -> cells B1:NB1
2. I have a cost of 100€ that I have to pay every month, on the 15th, nearest to the WORKDAY.

Anyone knows what formula to use in order to insert it in B2 (just below the first date) and drag it until the end of the year so 100€ would be filled under appropriate dates?
For example, 15.01.2017 is a Sunday, so 100€ would go under 16.01.2017 as it is the closest workday.

 

[Rick Rothstein]

Put this formula in cell B2 and copy it across to cell NB2...

=IF(DAY(B1)=15-(WEEKDAY(DATE(YEAR(B1),MONTH(B1),15))=7)+(WEEKDAY(DATE(YEAR(B1),MONTH(B1),15))=1),"100€","")

Thank you Rick, that works too!

You are welcome; however, this modification to my formula (which cuts out four function calls) should be more efficient...

=IF(DAY(B1)=15-(WEEKDAY(B1-DAY(B1)+15)=7)+(WEEKDAY(B1-DAY(B1)+15)=1),"100€","")

 

[Tetra201]

Another approach, shorter formula:

=IF(OR(AND(DAY(B1)=15,WEEKDAY(B1,2)<6),2*DAY(B1)+WEEKDAY(B1,2)=33),100,"")

 

[Rick Rothstein]

Another approach, shorter formula

But one additional function call though...

 

[Tetra201]

But one additional function call though...

Still a bit faster. On the same range of 100000 dates:
- my formula from Post #12: ~0.25 seconds
- your formula from Post #11: ~0.30 seconds

 

[Rick Rothstein]

Still a bit faster. On the same range of 100000 dates:
- my formula from Post #12: ~0.25 seconds
- your formula from Post #11: ~0.30 seconds

Hmm, surprising, but good to know. I guess an AND and OR function call is faster than a DAY function call.