Date Formula
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!
Answers
-
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.
-
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!
-
Are you able to provide some screenshots for context?
-
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.
-
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.
-
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?
-
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.
-
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.
-
That did it, thank you.
-
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?
-
Does this do it like you posted above?
WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 5)
-
@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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!