Last Working Day of the Month
I have been wracking and wrecking my brain trying to figure out a formula that will go ahead and automatically display the date of the last work day of the month taking into account weekends and holidays that will automatically calculate based on today's date.
Any ideas?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Comments
-
I don't trust most of the workday related functions.
There is no ISWORKDAY() function (you might ask for one, if you think it will help you)
If you only have to deal with weekends (there are no US holidays falling on the last day of the month), then:
I would create one column to get the last day of the month. That's not too difficult.
In a second column check for WEEKDAY(first column) = 1 (Sunday) or = 7 (Saturday) and then use either the unchanged value, subtract 2, or subtract 1.
If there are other complications, then the solution should be some variation of that.
Craig
-
Much obliged. Believe it or not It was getting the last day of the month that was giving me trouble. It was a long week last week.
Thanks Craig.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Oh, did you get it?
Here's what I use:
=IF(MONTH([Date]$4) = 12, DATE(YEAR($[Date]$4), 12, 31), DATE(YEAR($[Date]$4), MONTH([Date]$4) + 1, 1) - 1)
but I often use a table derived from that.
Craig
-
I did get it figured out. It's very similar to your's actually.
=IF(MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()), 12, 31))
I need it displayed on a dashboard, so I just threw an extra helper column in one of the other sheets and "hid" it in date format and plugged in...
=IF(WEEKDAY(IF(MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()), 12, 31))) = 1, IF(MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()), 12, 31)) - 2, IF(WEEKDAY(IF(MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()), 12, 31))) = 7, IF(MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()), 12, 31)) - 1, IF(MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()), 12, 31))))
I know I know... you aren't a fan of super long formulas (and yes I used copy/paste), but it got the job done for me. Haha.
I just used the EOM formula and then just built in the IF(WEEKDAY...) = 1, -2, IF(WEEKDAY...) = 7, -1, otherwise EOM.
The angle I had been looking at was trying to use a MAX function to look at every working day of the month. After reading your post about just pulling the last day of the month THEN comparing it to working days, I was finally able to get my mess pointed in the right direction.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
What's the point of the first column?
Craig
-
I realized that after posting and changed it. I just got done editing my original solution. Haha
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
OK, that makes more sense.
I don't know if
one TODAY() and then referencing that cell multiple times
or
calling TODAY() multiple times
is different. It could be the same or very different depending on the back-end code. My experimentation leads me to believe there is no way for me (the end-user) to really know. I open the same sheet in two different tabs and the CPU is different, before I even start to check timing, so I gave up.
However, since TODAY() is a function, I suspect the first is quicker.
If you ever run into the sheet slowing down, you might change that to see if it helps.
Now ... how can we account for non-working days that don't fall on a weekend? I think we'd need to be able to iterate over the determined date ... and work backwards until we find a working day. I've got some ideas on that, but for another day or a paying customer.
Craig
-
Referencing a cell with TODAY() in it vs referencing TODAY() multiple times ended up being about the same for me. TODAY() multiple times was 1 less keystroke per reference because of the column name, and it saved me one whole cell.
Since it is a rather small sheet (a whopping 6 rows and 4 columns), and I only needed to use the formula once, it doesn't have a noticeable effect on the speed of loading the sheet or making changes. It is only 5 items for inventory tracking being used in a metric widget with a reminder saying to send out the on hand to the client on the last working day of the month. I then used the formula to display the actual date to avoid confusion and to set up a reminder.
I've got some ideas on that, but for another day or a paying customer.
Fair enough. Haha. I have a couple of things I'm going to try as well. I don't know if I want to try to work in WORKDAY or if building a table and referencing that would be easier. Hmm...
Since the only holiday on the last day of the month here in the US is New Years Eve, I don't NEED it, but it certainly has the gears turning now.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
re: but it certainly has the gears turning now.
Sometimes, it is hard to turn those off.
I think WORKDAY (or any of Smartsheet's built-in formulas) will be difficult to implement. I was thinking about COLLECT() and a list of dates, but haven't gotten farther.
Craig
-
I always forget about COLLECT. I may think on that one as well.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!