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]

For example, 15.01.2017 is a Sunday, so 100€ would go under 16.01.2017 as it is the closest workday.

Does that mean if the 15th occurs on a Saturday, the Friday before is marked?

Do we have to take holidays into account? If so, where is your list of dates for the holidays located at?

 

[Domyzon]

Yes, the Friday before. There is no list for holidays.

 

[Tetra201]

Try this:

=IF(AND(DAY(B1)=15,WEEKDAY(B1,2)<6),100,IF(AND(DAY(B1)=14,WEEKDAY(B1,2)=5),100,IF(AND(DAY(B1)=16,WEEKDAY(B1,2)=1),100,"")))

 

[Domyzon]

Thanks, almost! It doesn't seem to work if the date is on Sunday...

I used 15-1 and 15+1 for 14 and 16, as if 15 was changing variable, determined in another cell.

 

[Tetra201]

... It doesn't seem to work if the date is on Sunday...

Example?

 

[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€","")

 

[Domyzon]

I apologise, I forgot to fix one value, it works! Thank you very much!

 

[Domyzon]

Thank you Rick, that works too!

 

[Tetra201]

... Thank you very much!

You are most welcome.