Last Working Day of the Month

Paul Newcome

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?

J. Craig Williams

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

Paul Newcome

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.

J. Craig Williams

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

Paul Newcome

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.

J. Craig Williams

What's the point of the first column?

Craig

Paul Newcome

I realized that after posting and changed it. I just got done editing my original solution. Haha

J. Craig Williams

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

 

Paul Newcome

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.

J. Craig Williams

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

Paul Newcome

I always forget about COLLECT. I may think on that one as well.