Last Working Day of the Month

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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?

Tags:

Comments

  • J. Craig Williams
    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
    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.yes

  • J. Craig Williams
    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
    Paul Newcome ✭✭✭✭✭✭
    edited 08/20/18

    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/pastedevil), 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
    J. Craig Williams ✭✭✭✭✭✭

    What's the point of the first column?

    Craig

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/20/18

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

  • J. Craig Williams
    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
    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
    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
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!