SOLVED

Date Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2114062%22%20slang%3D%22en-US%22%3EDate%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114062%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20get%20the%20date%20to%20%3D%20the%20last%20Friday%20of%20the%20month%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2114062%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114635%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834232%22%20target%3D%22_blank%22%3E%40jdilks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTODAY()%2BMOD(8-WEEKDAY(TODAY()%2C15)%2C7)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114605%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114605%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F942068%22%20target%3D%22_blank%22%3E%40bovasclion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114269%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114269%22%20slang%3D%22en-US%22%3E%3CP%3Euse%26nbsp%3B%3DCEILING(EOMONTH(TODAY()%2C-1)-5%2C7)%2B6%20t%20o%20get%20the%20first%20Friday%20of%20current%20month%20you%20can%20replace%20today()%20to%20refer%20cell%20to%20get%20first%20Friday%20of%20that%20month.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bovasclion_0-1612460590919.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F252330iD7FAD04676C9993B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bovasclion_0-1612460590919.png%22%20alt%3D%22bovasclion_0-1612460590919.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114240%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Hans%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20with%20the%20formula%20for%20the%20first%20Friday%20of%20the%20month%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114098%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114098%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834232%22%20target%3D%22_blank%22%3E%40jdilks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20If%20you%20want%20the%20last%20Friday%20in%20the%20month%20of%20the%20date%20that%20is%20in%20cell%20B2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DEOMONTH(B2%2C0)%2B1-WEEKDAY(EOMONTH(B2%2C0)-5)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20If%20you%20want%20the%20last%20Friday%20in%20the%20current%20month%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DEOMONTH(TODAY()%2C0)%2B1-WEEKDAY(EOMONTH(TODAY()%2C0)-5)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2114094%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2114094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834232%22%20target%3D%22_blank%22%3E%40jdilks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DEOMONTH(A1%2C0)-WEEKDAY(EOMONTH(A1%2C0)%2C15)%2B1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20in%20A1%20any%20date%20within%20given%20month.%20Or%20something%20like%20this%2C%20depends%20on%20how%20do%20you%20define%20year%20and%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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)