Using TODAY() as a date range
Hi All, I was hoping to be able to use the TODAY() function as part of a date range check using the IF and AND functions then using column name >= TODAY, column name <= TODAY() in a nested IF statment.
=IF(Complete1 = 1, "Up", IF(Start1 = TODAY(), "Angle Up", IF(AND(Start1 >= TODAY(), Finish1 <= TODAY()), "Sideways", IF(Finish1 < TODAY(), "Down"))))
Everything works it's just when I add the IF(AND(.... in the formula it returns nothing at all regardless of the date it is checking.
Comments
-
Try swapping the symbols in the AND statement.
Start1 <=
Finish1 >=
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks for the reply but I've tried removing the equals sign and just using greater than and less than but no joy
-
and also tried your suggestion and it still returning a blank result
-
You are missing important parts of your logic.
Is this what you are looking for?
1. Complete = Up
all below are Not Complete
2. Finish in Past = Down
3. Start is Today = Angle Up
4. Start in Future = ??
5. Start in Past = Sideways
Assumption: Finish always >= Start
If so, try this:
=IF(Complete@row = 1, "Up", IF(Finish@row < TODAY(), "Down", IF(Start@row > TODAY(), "TBD", IF(Start@row = TODAY(), "Angle Up", IF(Start@row < TODAY(), "Sideways", "Not Accounted For")))))
and change "TBD" to your choice for #4
Craig
-
I've tried getting the problem part to work in isolation and still can't
IF(AND(Start1 >= TODAY(), Finish1 <= TODAY()), "Sideways") s
any suggesstions would be welcomed. Oh and I replaced the TODAY function with the DATE function and it works.
Can anyone offer some insight into why the TODAY function can't be used int the above scenario? Just to give you some context as to why I need to use the TODAY function it is being applied across rolling dates
-
This formula:
IF(AND(Start1 >= TODAY(), Finish1 <= TODAY()), "Sideways")
is only "Sideways" when both Start and Finish are TODAY(), UNLESS your dates are backwards.
I don't know how you replaced TODAY() with DATE(). That's seems odd.
Did you read my previous post?
Craig
-
Hi Craig and thanks for the info so far. I replaced the TODAY function with a DATE nby setting a date range eg
=IF(AND(Start1 >= DATE(2018, 7, 18), Finish1 <= DATE(2018, 7, 24)), "Sideways")
-
this is a better screenshoot of what I'm trying to do
-
here it is using today function
-
I think I understand now, my logic is flawed as you mentioned in your original post when trying to figure out what I was trying to achieve. It can only be the actual day for it to work.
Thanks for your help Craig
-
keep up the good work Craig. I've seen you in other posts offering advice and resolving issues.
again thanks
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives