Display Dates

Matt Travis

Hi,

Another question for you clever ones. I don't know if i'm missing something simple or not. I would like to display the weekends within a year on a grid but I want this to update itself yearly. So for example in January this year a column would display "2nd -3rd January" then "9th -10th January" in the next row down and so on. Next year the weekends will be on different dates.

Is there any way I can display the weekend dates only and importantly have it update each year with user intervention? I know I can use the Weekday function to display a number for a particular day in the year so I assume what I want to do is possible somehow.

Paul Newcome

What if the 1st of January is on a Sunday?

Matt Travis

The 1st January is a bank holiday for us anyway so it won't matter for this.

Paul Newcome

Ok. Would you want to show something like "31 Dec - 1 Jan"? Which brings me to my next question... How would you want to display a month overlap? For example, in 2021 there is a weekend of 31 July - 1 Aug. That would look a little off using your previously stated format of "31st - 1st Aug".

By the way... We CAN do this. I just want to make sure we have all of the little details squared away so we can hopefully get it right the first time.

Matt Travis

Hi Paul,

Yes showing it that way would be fine. The weekend 31 July - 1 Aug you suggested would be perfect. If there is a way to show bank holidays too then that would be ideal but I didn't want to stretch it too far.

Thank you, happy to know it's possible and intrigued to learn.

Paul Newcome

Bank holidays would have to be manually entered somewhere since they may not always be the same number of days from the year.

The basic process for getting the dates generated would be to use an IF statement to generate the first weekend, then just keep adding 7.

So... Are you able to post a screenshot that has a few weekends manually populated so I can see exactly how you want this laid out.

You also mentioned "update each year with user intervention". DO you definitely want that as manual, or would you like to have that automated as well?

Matt Travis

Hi Paul,

I would like it to be automated so there is no user intervention needed on this. Added the screenshot below as requested. This is a snapshot of the current one.

Paul Newcome

Take a look at this sheet and let me know if you have any questions. A very basic explanation would be that the location of each formula listed is on the left of the gray bar. To the right is the working portion. I manually entered the date in the Year1 cell to show a weekend that overlaps both year and month. There is an automation set to run once per year to update the date to the current date which will contain the current year every year at 12:00am on January 1st. This way every year the weekends are up to date.

We use a table to convert month numbers into "Jan", "Feb", etc. These are manual entry, so you can change the text in the columns to whatever you want. There is also a table to add on the "st", "nd", "rd", or "th" depending on the day number.

Please let me know if it all makes sense or if I need to better clarify anything.

https://app.smartsheet.com/b/publish?EQBCT=51a1eadaab58455eaf2edb5999347a6c