Hi!
I am having an issue with my ‘Days Left’ column. This was screenshot on 1/22, so please read this chart as if it’s 1/22.
My issue is with the last row. It shouldn’t say ‘One Day Left’ since the event hasn’t started yet. Today is 1/22, it starts and ends on 1/23. I want this to display only for when an event has already started, so in this case, ‘Ends today’ should only display on 1/23.
I had an issue previously where an event ending ‘tomorrow’ displayed ‘Ending Today’. ex. the row above the last. To fix that I added a +1 to the math formula as seen below. It’s displaying as it should now.
Would anyone happen to know what I can do so events that last one day only displays ‘Ends Today’ on the date in question and no other days? Thanks!
Anyone have any idea what I may be doing wrong or missing?
Thanks!
Would CEILING work better? Then take out the +1? I’m guessing the time difference is throwing off the rounding a bit. What does your TodaysDate column formula look like? Also, does your EndDate have a time associated with it?
This is my TodaysDate Column:
and no, my EndDate column only has the date in short form.
But does it have an underlying time. Even though you are displaying a shortened date, it’s still calculating with time behind the scenes. Today is obviously gonna be using the current day and time, so it’s always changing. I’m trying to understand if the end date has a certain time associated with it, or if it’s always midnight, or always the same time of day?
I’m guessing that’s why you are using FLOOR to get rid of the decimal hours calculated after the days before the decimal. The problem most likely is whenever the time portion of today crosses the same time portion of the end date, the your number of days different before the decimal changes, and then when you floor it, you are always knocking it down to the lower value, which could sometimes be correct, but if the different is just a hair under your intended number of days, then you floor to one day to little. I’d have to play around a bit, but I would need to understand what the full end date/time is, do you want the number of days to change after today’s time passes the end date time or after midnight, and if the end date is today, is that when it should say 0 days left?
In the meantime, you could try ROUND instead of FLOOR to see if that gives you better results. I’m not 100% sure if that will work correctly.
3 Likes
I see! this is the End Date column:
I would like for the IF-Days Left Column to be blank if the date hasn’t started yet. If it lasts only one day, it should say Ends Today’ once the date in question begins (so yes midnight) If it’s more than one day long, it should say the number of days left once the date in question begins. It seems to work when an event is more than one day long, Im having an issue when an event is only one day long so in this case 1/23/21 to 1/23/21.
This is the last column depicting that:
Let me try the ROUND swap.
