Function to Move Date if it is a Weekend

[Sophie.Durrant]

I thought I knew how to do this already, but had to make it more complicated for myself!

I have a list of monthly payments and the day of the month they are scheduled to go out is in column A. I want a function in column I to tell me what day the payment will actually go out if the date in column A is a weekend.

My problem is I am not putting a full date in column A, so I need a way to identify the number in column A as the numbered day of the current month. For example, if I have the number 8 in column A, the 8th December was a Saturday so I want the date in Column I to be the following Monday, 10th December.

Any ideas please?

[Andrew-R]

If your 'date' is in A1 then try this formula in I1:

=A1+MAX(3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),A1),16),0)

However, it's a bad idea to have your dates dynamic in this way - you'd be much better off explicitly declaring the month and year somewhere on your sheet.

[Melvinrobb]

Put the following formula in B1 and drag down:

Please Login or Register  to view this content.

[Sophie.Durrant]

Thanks for your reply Andrew, could you please explain what this formula is saying? This will help me to understand so I might be able to use it again in future.

Also, why is it a bad idea to have the dates like this? I am quite lazy so have lots of functions like this built in to my spread sheets so I only have to work on them once... automation, automation, automation!!!

[Andrew-R]

Yes, I can explain. This is my whole formula:

=A1+MAX(3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),A1),16),0)

The section in bold converts the number in cell A1 to a date - using A1 as the day number and the current month and year.

We wrap this date inside WEEKDAY(<date>,16) which returns a day of the week value, 1 for Saturday, 2 for Sunday, 3 for Monday, etc.

We subtract this value from 3, so Saturday becomes 2, Sunday 1, Monday 0, Tuesday -1....

This is then put inside a MAX statement which picks the larger of the value returned and zero, so it will return 2 for a Saturday, 1 for a Sunday and 0 for Monday-Friday.

Finally we add this to the day in A1, so if that day is a Saturday then it will add 2, if it's a Sunday it will add one and for all other days it will remain unchanged - which, happily, is exactly what you wanted

It's a bad idea to have the dates dynamic because if you, say, work on your spreadsheet today then save it and don't reopen it until Wednesday 3rd January it will recalculate all of the cells so that they relate to January, rather than December, because it's using the TODAY() function, which by it's nature, changes day to day.

So, today, on the row where you have an 8 my formula is returning 10, because 8th December is a Saturday, but when you reopen the sheet in January the formula will return 8, because 8th January is a Tuesday. So long as you know it's going to do that then it's fine, but it would confuse the hell out of me

[daddylonglegs]

Note that WEEKDAY(date,16) is only available in Excel 2010 or later versions - this formula will work in Excel 2003 using Andrew's approach

=A1+MAX(3-WEEKDAY(TODAY()-DAY(TODAY())+A1+1),0)