• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Weekend date require dynamically

Hi Team

In our process, last friday of every month is last day of month, at that day, month ends, and next day, saturday, a new month starts. for example, in sep 2014, month ended on 26-sep because it was last friday. then new Oct month started from 27-sep.
What I am looking for I want every friday date in columns dynamically irrespective of numbers of friday in a month. I am attaching a sample file.

When next month comes, it changes automatically. Please advise is it possible?
 

Attachments

  • Test weekend.xlsx
    11.6 KB · Views: 9
Hi Team

In our process, last friday of every month is last day of month, at that day, month ends, and next day, saturday, a new month starts. for example, in sep 2014, month ended on 26-sep because it was last friday. then new Oct month started from 27-sep.
What I am looking for I want every friday date in columns dynamically irrespective of numbers of friday in a month. I am attaching a sample file.

When next month comes, it changes automatically. Please advise is it possible?
Hi,

Fot the last Friday based upon a date we can use this with a date in g2.

=DATE(YEAR(G2),MONTH(G2)+1,1)-WEEKDAY(DATE(YEAR(G2),MONTH(G2)+1,1)-6)

Or for the last Friday of the current month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-6)

See your workbook.
 

Attachments

  • Test weekend(1).xlsx
    12 KB · Views: 5
Hi,

Fot the last Friday based upon a date we can use this with a date in g2.

=DATE(YEAR(G2),MONTH(G2)+1,1)-WEEKDAY(DATE(YEAR(G2),MONTH(G2)+1,1)-6)

Or for the last Friday of the current month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-6)

See your workbook.
Hey Mike

Amazing work, But what I am looking for is if there are five fridays in a month then I want the dates of those friday, lets say if there are four fridays in november, then four friday must be there, when month changes, then it changes to december fridays
 
Hey Mike

Amazing work, But what I am looking for is if there are five fridays in a month then I want the dates of those friday, lets say if there are four fridays in november, then four friday must be there, when month changes, then it changes to december fridays
Hi,

For any date in A1. Put this in a cell and drag down for all the Fridays. It returns blanks when it runs out of Fridays.

=IF(MONTH($A$1-DAY($A$1)+(ROW(A1)-1)*7+8-WEEKDAY($A$1-DAY($A$1)-5))=MONTH($A$1),$A$1-DAY($A$1)+(ROW(A1)-1)*7+8-WEEKDAY($A$1-DAY($A$1)-5),"")

Or automatically for the current month.

=IF(MONTH(TODAY()-DAY(TODAY())+(ROW(A1)-1)*7+8-WEEKDAY(TODAY()-DAY(TODAY())-5))=MONTH(TODAY()),TODAY()-DAY(TODAY())+(ROW(A1)-1)*7+8-WEEKDAY(TODAY()-DAY(TODAY())-5),"")
 
Back
Top