Using dates - If a calculated date falls on a weekend, how do I get Excel to return the date of the Friday before?

[andrewrgmartin]

Hi, I'm putting together a cash flow forecast and am trying to work out the payment dates for our invoices based on invoice date + terms (30, 60, 90 etc) + paydate (some are paid on the 1st, some on the 3rd, some every Thursday, and some on the last working day of the month). At the moment some of my calculated payment dates fall on a weekend. How do I get Excel to give me the Friday date rather than say the Saturday or Sunday date? All help appreciated.

 

[MisterBates]

use weekday([date], 2) to get the day of the week with Monday as day 1, Sunday as Day 7, and max() to convert days 6 and 7 to day 5.

 

[Domski]

Another way: =A1-CHOOSE(WEEKDAY(A1,2),0,0,0,0,0,1,2)

Dom

 

[andrewrgmartin]

Thanks MisterBates and Domski. The table below shows an invoice that is on 120 day terms and is paid on the 3rd working day of the following of the following month. The result shows that the payment is due on a Saturday. Can you explain how I incorporate your formula in to mine to ensure the result is always a week day please?

[TABLE="width: 300"]
<TBODY>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]invoice date[/TD]
[TD]22/6/12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]payment date[/TD]
[TD]=EOMONTH(D1,4)+B1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]result[/TD]
[TD]3/11/12[/TD]
[/TR]
</TBODY>[/TABLE]

Thanks,
Andrew

 

[Domski]

Try:

=EOMONTH(D1,4)+B1-CHOOSE(WEEKDAY(EOMONTH(D1,4)+B1,2),0,0,0,0,0,1,2)

Dom

 

[andrewrgmartin]

Cheers Dom. Now that I have a result that is a week day I'd like to use that date and get Excel to compare it to a fixed table of dates and then return the next available one. i.e I have a separate table of dates that are all Thursdays. If i pay a supplier every Thursday and my calculated payment date works out to be a Wednesday, how do I get Excel to return the date of the next Thursday? Thanks for your continued help, Andrew

 

[Domski]

Try: =A1-WEEKDAY(A1+2)+7

Dom

 

[andrewrgmartin]

Thanks, that's great. How about if I want it to return the 3rd working day of the month (needs to ignore weekends and bank hols)? I already have a table that lists these dates, can I link to it via a Lookup?

 

[barry houdini]

You could probably use WORKDAY function for some of these, e.g. if payment date is

=EOMONTH(D1,4)+B1

you can make sure that falls on a weekday by using this formula

=WORKDAY(EOMONTH(D1,4)+B1+1,-1)

For third workday of the month what are your inputs, or is it the 3rd workday of the month after a certain date? If it's the latter then with a list of 3rd workdays (in ascending order) in Z2:Z100 you can get the next one on or after a date in A1 by using LOOKUP, i.e.

=LOOKUP(A1,Z2:Z100)

 

[andrewrgmartin]

You could probably use WORKDAY function for some of these, e.g. if payment date is

=EOMONTH(D1,4)+B1

you can make sure that falls on a weekday by using this formula

=WORKDAY(EOMONTH(D1,4)+B1+1,-1)

For third workday of the month what are your inputs, or is it the 3rd workday of the month after a certain date? If it's the latter then with a list of 3rd workdays (in ascending order) in Z2:Z100 you can get the next one on or after a date in A1 by using LOOKUP, i.e.

=LOOKUP(A1,Z2:Z100)

The first two formulas worked ok, but unfortunately the 3rd returned a date before the value in A1. My table is in ascending order, e.g. 5/1/12, 3/2/12, 5/3/12 etc - any ideas?