Weekday formula question
I have a formula in Excel where I need it to return the Monday date from the week in the Inception Week column. For example 3/9/18 is a Thursday, but 3/5/18 is the Monday of that week. This formula works good except when the Monday of that week has to switch back to the previous month, like with the 3/1/18 date, the Monday of that week was 2/25/18 and the formula errors. Any ideas what I need to add to this formula to get that Monday date to calculate correctly for a previous month?
Inception Week formula
=IF([Customer Since Date]87 = "In Process", "", (DATE(YEAR([Customer Since Date]87), MONTH([Customer Since Date]87), DAY([Customer Since Date]87) + 2 - WEEKDAY([Customer Since Date]87))))
Best Answer
-
Try this instead...
=IF([Customer Since Date]87 = "In Process", "", DATE(YEAR([Customer Since Date]87), MONTH([Customer Since Date]87), DAY([Customer Since Date]87)) + 2 - WEEKDAY([Customer Since Date]87))
Answers
-
Try this instead...
=IF([Customer Since Date]87 = "In Process", "", DATE(YEAR([Customer Since Date]87), MONTH([Customer Since Date]87), DAY([Customer Since Date]87)) + 2 - WEEKDAY([Customer Since Date]87))
-
Very cool Paul thank you. I had too many parentheses LOL
-
Happy to help. 👍️
I also shifted one of the parenthesis to close out the DATE function before adding 2 and subtracting the weekday. This way you are doing the +/- to the date itself instead of to the DAY number.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!