Date Formula

Tammy Simpson

Hi! How can I do date formulas in the same column where some need to include weekends in the day count and others don't?

Thanks!

Paul Newcome

Are you able to provide more detail and a few examples of what you are trying to accomplish? Screenshots are also very helpful if possible.

Tammy Simpson

Hi! Yes! I have two different date situations. First, I have some reports that are due on the 10th business day (BD). Second, I have some reports that are due on the 15th of each month. So, if we use February 2021 as an example month, the 10th BD would be 2/12/21 and the 15th of the month would be 2/15/21.

First situation using BDs:

I have been using the workday formula using 2/1/21 as the start date.

=WORKDAY([Start Date]42, 10) - so if I use this formula, it gives me 2/15/21, when I expected it to give me 2/12/21. Does it not start the day count using 2/1/21? Is day one 2/2/21? What do you suggest for this type of date formula?

Second situation using calendar days:

I have one date column and putting formulas in depending on the due date of the report. So, the whole column can't be the same type of formula. Is there a way to use a formula that will give me the 15th of the month using calendar days, meaning including weekends in the day count?

Does that help at all? Thanks!

Paul Newcome

Are you able to provide some screenshots for context?

Tammy Simpson

I'm not exactly sure what you need to see in a screenshot. I have a list of tasks in excel that I am migrating to Smartsheet and setting up formulas for each of the different types of due dates.

Donni Matthew

I have this same scenario AND I have one date that is "due" on the 5th of the month. So, interested in the solution to the above.

Paul Newcome

How are you determining which one needs to be the 15th and which one needs to be the 10 business days? Do you have a column that specifically designates this?

Donni Matthew

Yes, I have a "client" column that determines the # of days. I am using this formula for the calendar vs business days:

=IF(Client@row = "Bank of America", WORKDAY([Date Received]@row, +2), IF(Client@row = "Fay", WORKDAY([Date Received]@row, +5), IF(Client@row = "Midfirst", WORKDAY([Date Received]@row, +5), IF(Client@row = "PennyMac", WORKDAY([Date Received]@row, +5), IF(Client@row = "Quicken", WORKDAY([Date Received]@row, +3), IF(Client@row = "Cenlar", WORKDAY([Date Received]@row, +3), IF(Client@row = "Shellpoint", WORKDAY([Date Received]@row, +5), IF(Client@row = "Nationstar", [Date Received]@row + 15, IF(Client@row = "Specialized", [Date Received]@row + 7, " ")))))))))

It is working fine. But, I don't have a solution for the 5th business day of the current month.

Paul Newcome

5th business day of CURRENT month would look something along the lines of...

WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 5)

Basically you are going to use the WORKDAY function and use the first of the current month as your starting date. To get the first of the current month, you use a DATE function and say the current year, the current month, and day 1.

Donni Matthew

That did it, thank you.

Tammy Simpson

Hi! Yes, I have a column. You can see it in the screen shot. How do I do the 15th of the month, NOT business days?

Tammy Simpson

Does this do it like you posted above?

WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 5)

Paul Newcome

@Donni Matthew Happy to help. 👍️

@Tammy Simpson If all you need is the 15th of the current month, try this...

=DATE(YEAR(TODAY()), MONTH(TODAY()), 15)