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.

 

[barry houdini]

Yes, sorry, LOOKUP will give you the previous date. Try either this "array formula"

=MIN(IF(Z2:Z100>=A1,Z2:Z100))

confirmed with CTRL+SHIFT+ENTER

...or a non array version

=INDEX(Z2:Z100,MATCH(A1-1,Z2:Z100)+1)

....or in fact you can get the next "3rd Workday" diectly without a list (except a holiday list) by using this formula in Excel 2007 or later

=LOOKUP(A1-1,WORKDAY(EOMONTH(A1,{-2,-1;-1,0}),3,H$1:H$10))

where H1:H10 contains Bank Holiday dates

 

[andrewrgmartin]

Yes, sorry, LOOKUP will give you the previous date. Try either this "array formula"

=MIN(IF(Z2:Z100>=A1,Z2:Z100))

confirmed with CTRL+SHIFT+ENTER

...or a non array version

=INDEX(Z2:Z100,MATCH(A1-1,Z2:Z100)+1)

....or in fact you can get the next "3rd Workday" diectly without a list (except a holiday list) by using this formula in Excel 2007 or later

=LOOKUP(A1-1,WORKDAY(EOMONTH(A1,{-2,-1;-1,0}),3,H$1:H$10))

where H1:H10 contains Bank Holiday dates

Thanks Barry, that was a great help.

 

[Teligence]

Formulae are beautiful things - Understanding them is PFM (Pure Friggin' Magic)!!!

I had a similar need: We run checks en masse every Friday, so I need something to calculate a "check run" date for a Friday at least 7 days prior to an invoice due date to allow for our USPS snail mail to pickup and deliver before the actual Due Date. I was able to modify Dom's first formula to accomplish this as follows:
=K6-CHOOSE(WEEKDAY(K6,2),10,11,12,13,7,8,9)

Thanks, Dom!