TODAY()

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 and =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?

1 Like

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)

@Matt-a443 @Beij my apologies the formula I suggested was wrong and should instead should be either:

=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.

2 Likes

I have a work order sheet like this:

I have summary sheet like this: image

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.

  1. How to rectify this?
  2. Which formula to use?
  3. 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.

1 Like

Hi Isaiah,
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.

Regards
Harsha

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.

1 Like

No. It is not working. It is showing as below:
image

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)

Hi @Harshamaddur I have retrieved this post: UTC & Time Zone Conversion
Maybe it can help you.

1 Like

Joel-1ada,
Thanks for replying. I tried UTC & Time Zone Conversion. But, it didn't help.

1 Like

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

2 Likes

@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.

1 Like

=DATE(YEAR(NOW()-(7/24)),MONTH(NOW()-(7/24)),DAY(NOW()-(7/24))) I am confused.

  1. Is this a single formula or 3 different formulas? Because you used '=' only behind 'DATE' not behind 'MONTH' & 'DAY'
  2. 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?
  3. 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)
  4. Also, IST is 5:30 hours ahead. Should I use 5.30/24 or 5:30/24 or 5.5/24.

Regards
Harsha

1 Like
  1. It's a single formula
  2. Type YEAR, MONTH, and DAY. They are Honeycode functions. You can see all the Honeycode functions here.
  3. No, you don't need the parenthesis around the division.
  4. 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.

2 Likes

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.

5 Likes

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

1 Like


Yep it recalculates as the time changes, this is old table opened today

1 Like

In work order sheet, I changed the dates to 26, 27, & 28 as follows

Then, I went to summary sheet and applied the formula as follows. But it is showing error