Excel formula to return closest friday 30 days out.

July 25, 2018 5:19 PM

Our accounting department wants us to put our invoice due date as the friday before the actual due date 30 days out and I would like to automate it. Please help

With our net 30 accounts if an invoice is dated 7/25 and is due 8/20 our internal due date is 8/17. Can I write a formula to automatically calculate this date? I'm pretty sure it will involve the WEEKDAY formula and some math that I can't figure out.

posted by Uncle to Computers & Internet (5 answers total)

Use WEEKDAY on the due date. Then subtract this value+1.

i.e. WEEKDAY of 8/20 (a Monday) is 2, using the default return type (Sun(1) to Sat(7))

Adding 1, you get 3, which is the number of days you need to subtract from the actual due date to get the invoice due date.

So if A1 is the actual due date, the formula to put the preceding Friday into B1 is:
= A1 - (WEEKDAY(A1) +1)
posted by pipeski at 5:31 PM on July 25, 2018 [4 favorites]

Seconding pipeski. That works.
posted by beagle at 5:39 PM on July 25, 2018

Best answer: Actually, that doesn't quite work. It gets the wrong Friday for Saturdays!
You need to add a modulus function:

= A1 - (MOD(WEEKDAY(A1),7) + 1)
posted by pipeski at 5:44 PM on July 25, 2018 [4 favorites]

If the actual due date is a Friday, do they want the adjusted date to be a week before that?
posted by thelonius at 10:08 PM on July 25, 2018

Best answer: Yeah, if you want Friday invoices to be due on the same day, and not the preceding Friday, you can use:

=IF(WEEKDAY(A1)=6, A1, A1-(MOD(WEEKDAY(A1),7)+1))
posted by pipeski at 4:14 AM on July 26, 2018