It's 5PM on 6/24/2020 PST, but TODAY() is returning 6/25/2020. How do I fix this?
Hey @Beij, this is something we need to address in our subsequent product updates. The reason you're seeing
6/25/2020 when using
=TODAY() function is because all Honeycode time is in UTC.
Depending on your use case you can adjust your expression to
=TODAY()-7/24 which should return you the date current for those of us in Pacific timezone.
Of course, you would need to make adjustments during Daylight Savings. Your expression would have to switch between
=Today()-7/24 depending on whether it's PST or PDT.
SEE POST BELOW
@Oleg I tried =Today()-7/24. Now it is 6/27/2020 11:13AM PST, and it's returning 6/26/2020. You sure Today() - 7/24 is the right formula?
Having the same exact issue as Beij, both aspects. (Wrong day both ways depending on time) Dont think -7/24 is right answer. (I tried -4/24 for EST)
=now()-7/24 which would return you the date and time, or
=date(year(now()-(7/24)), month(now()-(7/24)), day(now()-(7/24))) which would return the date only.
=today() will always count from midnight, not from the current time which is what was causing the results to be wrong.
I'll fix my original reply as well.
I have a work order sheet like this:
I have summary sheet like this:
My intention is to get no. of Today's workorders, no. of upcoming workorders and no. of previous work orders and no. of all work orders. So I used functions in summary sheet as follows:
1.In Today row & workorders column: =FILTER(Workorders,"Workorders[Date]=TODAY()")
2. In Upcoming row & workorders column: =FILTER(Workorders,"Workorders[Date]>TODAY()")
3. In All row & workorders column: =FILTER(Workorders)
4. In Previous row & workorders column: =FILTER(Workorders,"Workorders[Date]<TODAY()")
My time zone is IST. My problem is: In Summary sheet it shows yesterdays work orders as Today's in Today row.
It is creating a lot of confusion.
- How to rectify this?
- Which formula to use?
- Where to use that formula? In summary sheet (In Workorder column)?? or In Workorders sheet (in Date column)?
In 1, 2 and 4, try replacing TODAY() with NOW()+5.5/24.
Thanks for the reply.
I will wait till midnight 12 (When IST will become 23rd July & UTC will remain 22nd July) and do.
Now, at this time, both dates (IST & UTC ) are same.
Also, Please tell me which format should I use in 'Date' column in Workorder sheet/table.
Happy to help!
You don't need to wait until midnight. It doesn't matter when you make the change.
You can use the Date format or Date & time format, whichever you prefer.
No. It is not working. It is showing as below:
Today's date as per IST is 23rd. Dates in workorder sheet are 21st, 22nd & 23rd. So, in summary sheet it should be Today 1(23rd), Upcoming 0(none), All 3, and previous 2(21st and 22nd)
Thanks for replying. I tried UTC & Time Zone Conversion. But, it didn't help.
try using this =DATE(YEAR(NOW()-(7/24)),MONTH(NOW()-(7/24)),DAY(NOW()-(7/24))) instead of today. 7/24 is to account for UTC to PST conversion so adjust accordingly. Add it as extra column in filter table and use that in the filter formula, and make sure the dates formatted as dates and not auto formatted
@Harshamaddur I think this has to do with when a new calc is triggered. I don’t think the tables recalc every few minutes just in case there’s a formula like today() in them.
I think your filter formula made the computation at the moment you hit the enter key. Then, it won’t re-calculate until some data in one of the tables changes.
Instead, you could do this exact same formula in the app rather than the table. Save the formula to a data cell on an app screen. When the screen loads, the calculation will be performed and you get the freshest data.
=DATE(YEAR(NOW()-(7/24)),MONTH(NOW()-(7/24)),DAY(NOW()-(7/24))) I am confused.
- Is this a single formula or 3 different formulas? Because you used '=' only behind 'DATE' not behind 'MONTH' & 'DAY'
- Do I have to type YEAR, MONTH AND DAY as it is or do I have to type 2020 for YEAR, July for MONTH, 24 for DAY?
- Do I have to use brackets (paranthesis) around 7/24. Last time I didn't use brackets. (In my case it is 5.5/24)
- Also, IST is 5:30 hours ahead. Should I use 5.30/24 or 5:30/24 or 5.5/24.
- It's a single formula
- Type YEAR, MONTH, and DAY. They are Honeycode functions. You can see all the Honeycode functions here.
- No, you don't need the parenthesis around the division.
- You will want to use NOW()+5.5/24.
I think @AndrewB is right, though, in pointing out the formulas will not recalculate as the time changes, causing the results to become stale over time. If you don't want to move the formulas into an app, you could try updating them at 12:01 am everyday using an automation.
Thank you and @AndrewB for the detailed explanation. I will try this and let you know. I am happy to be in this community of such a helpful souls.
Its single formula, you should be able to just copy paste it. I think you should use 5.5/24
As to recalculation, I use it both in back end and app more in app in app it recalculates, back end well now I need to check that lol