SOLVED

Date Formula

Copper Contributor

How do I get the date to = the last Friday of the month?

6 Replies

@jdilks 

That could be

=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0),15)+1

if in A1 any date within given month. Or something like this, depends on how do you define year and month.

best response confirmed by jdilks (Copper Contributor)
Solution

@jdilks 

1) If you want the last Friday in the month of the date that is in cell B2:

 

=EOMONTH(B2,0)+1-WEEKDAY(EOMONTH(B2,0)-5)

 

2) If you want the last Friday in the current month:

 

=EOMONTH(TODAY(),0)+1-WEEKDAY(EOMONTH(TODAY(),0)-5)

 

@Hans Vogelaar 

Hi Hans,

 

Thank you for your help.

 

Can you help me with the formula for the first Friday of the month?

 

Thank you,

Jim

use =CEILING(EOMONTH(TODAY(),-1)-5,7)+6 t o get the first Friday of current month you can replace today() to refer cell to get first Friday of that month.

bovasclion_0-1612460590919.png

 

@bovasclion 

 

Hi,

 

Thank you for your help.

 

Jim

@jdilks 

As variant

=TODAY()+MOD(8-WEEKDAY(TODAY(),15),7)

 

1 best response

Accepted Solutions
best response confirmed by jdilks (Copper Contributor)
Solution

@jdilks 

1) If you want the last Friday in the month of the date that is in cell B2:

 

=EOMONTH(B2,0)+1-WEEKDAY(EOMONTH(B2,0)-5)

 

2) If you want the last Friday in the current month:

 

=EOMONTH(TODAY(),0)+1-WEEKDAY(EOMONTH(TODAY(),0)-5)

 

View solution in original post