Calculate 28th of month with exception

[lapta301]

Hi
I’m looking for one formula to calculate the day a payment is made.

Let me explain.

I have a date in A1 which could be any day in the month and in A2 onwards I need to show the 28th day of the month following the date in A1 or the previous working day if the 28th is a weekend.

For example using 1st January 19 in A1 the formula would bring up 28th January as that is the next 28th after the 1st and it is a normal workday.

However if the date in A1 was 29th January then the next 28th would be 28th Feb which be ok because that again is a normal workday.

Also if the date in A1 is 28th if the month then the formula should work out the next 28th not the same 28th that is in A1.

Looking at the calendar the next month where the 28th is a weekend is in April.

I hope that this makes sense and as ever any help would be appreciated.

 

[Dave Patton]

Excel 2010
	A
1	Tue Jan 1, 2019
2	Mon Jan 28, 2019
3
2d

Cell Formulas
Range	Formula
A2	=IF(DAY(A1)<28,WORKDAY.INTL(A1-DAY(A1)+28+1,-1,1),WORKDAY.INTL(EDATE(A1-DAY(A1)+28,1)+1,-1,1))

If you want to consider holidays, list the holidays in a range and add that range to the 2 WORKDAY.INTL functions.

 

[lapta301]

Hi Dave

Thank you for taking an interest and your response.

The formula seems to work correctly for a few months but then breaks down.

Here is a list of this year's months with the formula results on the left and what I am looking for in the centre and a different starting date on the right using your formula.


Tuesday 01/01/2019 Tuesday 01/01/2019 Monday 28/01/2019

Monday 28/01/2019 Monday 28/01/2019 Thursday 28/02/2019
Thursday 28/02/2019 Thursday 28/02/2019 Thursday 28/03/2019
Thursday 28/03/2019 Thursday 28/03/2019 Friday 26/04/2019
Friday 26/04/2019 Friday 26/04/2019 Friday 26/04/2019
Friday 26/04/2019 Tuesday 28/05/2019 Friday 26/04/2019
Friday 26/04/2019 Friday 28/06/2019 Friday 26/04/2019
Friday 26/04/2019 Friday 26/07/2019 Friday 26/04/2019
Friday 26/04/2019 Wednesday 28/08/2019 Friday 26/04/2019
Friday 26/04/2019 Friday 27/09/2019 Friday 26/04/2019
Friday 26/04/2019 Monday 28/10/2019 Friday 26/04/2019
Friday 26/04/2019 Thursday 28/11/2019 Friday 26/04/2019
Friday 26/04/2019 Friday 27/12/2019 Friday 26/04/2019

 

[steve the fish]

Try this:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>27),29),-1)

 

[lapta301]

Steve

Hi and again many thanks for your input. Unfortunately like Dave’s formula it breaks down after Friday 26th April with all months thereafter also showing 26th April.

 

[steve the fish]

Im afraid that will be user error. Its impossible for that formula, nor daves, to always show 26th April for all months after 26th April. Could you supply a list of dates that you are using to test the formula?

 

[MARK858]

Steve

Hi and again many thanks for your input. Unfortunately like Dave’s formula it breaks down after Friday 26th April with all months thereafter also showing 26th April.

Really? I'd check the source data.

Excel Workbook
	A	B	C
1	28/01/2019	28/02/2019	Thu
2	28/02/2019	28/03/2019	Thu
3	28/03/2019	26/04/2019	Fri
4	26/04/2019	26/04/2019	Fri
5	24/05/2019	28/05/2019	Tue
6	21/06/2019	28/06/2019	Fri
7	19/07/2019	26/07/2019	Fri
8	16/08/2019	28/08/2019	Wed
9	13/09/2019	27/09/2019	Fri
10	11/10/2019	28/10/2019	Mon
11	08/11/2019	28/11/2019	Thu
12	06/12/2019	27/12/2019	Fri
13	03/01/2020	28/01/2020	Tue
Sheet1

 

[FormR]

Hi, here is another option:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+IF(DAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),29),-1))>DAY(A1),0,1),29),-1)

This returns the 28th May 2019 for an input date of 26th April 2019.

 

[lapta301]

Hi one and all
The source data is merely a real date in A1 28/1/19 followed by the formula provided and copied down like so. I have also tried using 1/1/19 as a starting point

Mon 28/01/2019 28/01/2019
Thu 28/02/2019 =WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>27),29),-1)
Thu 28/03/2019 =WORKDAY(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>27),29),-1)
Fri 26/04/2019 =WORKDAY(DATE(YEAR(A3),MONTH(A3)+(DAY(A3)>27),29),-1)
Fri 26/04/2019 =WORKDAY(DATE(YEAR(A4),MONTH(A4)+(DAY(A4)>27),29),-1)
Fri 26/04/2019 =WORKDAY(DATE(YEAR(A5),MONTH(A5)+(DAY(A5)>27),29),-1)
Fri 26/04/2019 =WORKDAY(DATE(YEAR(A6),MONTH(A6)+(DAY(A6)>27),29),-1)
Fri 26/04/2019 =WORKDAY(DATE(YEAR(A7),MONTH(A7)+(DAY(A7)>27),29),-1)
Fri 26/04/2019 =WORKDAY(DATE(YEAR(A8),MONTH(A8)+(DAY(A8)>27),29),-1)

If I have made an error then please let me know.


FormR thank you also for showing an interest in this and I am very pleased to say that your formula works fine as shown below


Mon 28/01/2019 28/01/2019
Thu 28/02/2019 =WORKDAY(DATE(YEAR(A1),MONTH(A1)+IF(DAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),29),-1))>DAY(A1),0,1),29),-1)
Thu 28/03/2019 =WORKDAY(DATE(YEAR(A2),MONTH(A2)+IF(DAY(WORKDAY(DATE(YEAR(A2),MONTH(A2),29),-1))>DAY(A2),0,1),29),-1)
Fri 26/04/2019 =WORKDAY(DATE(YEAR(A3),MONTH(A3)+IF(DAY(WORKDAY(DATE(YEAR(A3),MONTH(A3),29),-1))>DAY(A3),0,1),29),-1)
Tue 28/05/2019 =WORKDAY(DATE(YEAR(A4),MONTH(A4)+IF(DAY(WORKDAY(DATE(YEAR(A4),MONTH(A4),29),-1))>DAY(A4),0,1),29),-1)
Fri 28/06/2019 =WORKDAY(DATE(YEAR(A5),MONTH(A5)+IF(DAY(WORKDAY(DATE(YEAR(A5),MONTH(A5),29),-1))>DAY(A5),0,1),29),-1)
Fri 26/07/2019 =WORKDAY(DATE(YEAR(A6),MONTH(A6)+IF(DAY(WORKDAY(DATE(YEAR(A6),MONTH(A6),29),-1))>DAY(A6),0,1),29),-1)
Wed 28/08/2019 =WORKDAY(DATE(YEAR(A7),MONTH(A7)+IF(DAY(WORKDAY(DATE(YEAR(A7),MONTH(A7),29),-1))>DAY(A7),0,1),29),-1)
Fri 27/09/2019 =WORKDAY(DATE(YEAR(A8),MONTH(A8)+IF(DAY(WORKDAY(DATE(YEAR(A8),MONTH(A8),29),-1))>DAY(A8),0,1),29),-1)
Mon 28/10/2019 =WORKDAY(DATE(YEAR(A9),MONTH(A9)+IF(DAY(WORKDAY(DATE(YEAR(A9),MONTH(A9),29),-1))>DAY(A9),0,1),29),-1)
Thu 28/11/2019 =WORKDAY(DATE(YEAR(A10),MONTH(A10)+IF(DAY(WORKDAY(DATE(YEAR(A10),MONTH(A10),29),-1))>DAY(A10),0,1),29),-1)
Fri 27/12/2019 =WORKDAY(DATE(YEAR(A11),MONTH(A11)+IF(DAY(WORKDAY(DATE(YEAR(A11),MONTH(A11),29),-1))>DAY(A11),0,1),29),-1)
Tue 28/01/2020 =WORKDAY(DATE(YEAR(A12),MONTH(A12)+IF(DAY(WORKDAY(DATE(YEAR(A12),MONTH(A12),29),-1))>DAY(A12),0,1),29),-1)
Fri 28/02/2020 =WORKDAY(DATE(YEAR(A13),MONTH(A13)+IF(DAY(WORKDAY(DATE(YEAR(A13),MONTH(A13),29),-1))>DAY(A13),0,1),29),-1)
Fri 27/03/2020 =WORKDAY(DATE(YEAR(A14),MONTH(A14)+IF(DAY(WORKDAY(DATE(YEAR(A14),MONTH(A14),29),-1))>DAY(A14),0,1),29),-1)
Tue 28/04/2020 =WORKDAY(DATE(YEAR(A15),MONTH(A15)+IF(DAY(WORKDAY(DATE(YEAR(A15),MONTH(A15),29),-1))>DAY(A15),0,1),29),-1)
Thu 28/05/2020 =WORKDAY(DATE(YEAR(A16),MONTH(A16)+IF(DAY(WORKDAY(DATE(YEAR(A16),MONTH(A16),29),-1))>DAY(A16),0,1),29),-1)
Fri 26/06/2020 =WORKDAY(DATE(YEAR(A17),MONTH(A17)+IF(DAY(WORKDAY(DATE(YEAR(A17),MONTH(A17),29),-1))>DAY(A17),0,1),29),-1)


And again I have also used 1/1/19 as a starting point and it works fine.


Thank you all for your time which is very much appreciated

 

[steve the fish]

If you just wanted to create a list of dates then you could use this and drag down:

=WORKDAY(DATE(2019,ROWS($A$1:A1),29),-1)