TODAY()

[Harshamaddur]

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.

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)?

[Isaiah]

In 1, 2 and 4, try replacing TODAY() with NOW()+5.5/24.

[Harshamaddur]

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

[Isaiah]

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.

[Harshamaddur]

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)

[Joel-1ada]

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

[Harshamaddur]

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

[Julsi]

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

[AndrewB]

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

[Harshamaddur]

=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

[Isaiah]

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.

[Harshamaddur]

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.

[Julsi]

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

[Julsi]

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

[Harshamaddur]

@Harshamaddur

=DATE(YEAR(NOW()-(7/24)),MONTH(NOW()-(7/24)),DAY(NOW()-(7/24)))

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

[Julsi]

So for some reason it only works if you create extra column with date
and then do filtering

[Isaiah]

It looks like you're missing a quote (") in your B2 equation between the two closing parens at the very end.

[Harshamaddur]

I added quotes, before year and between parenthesis in the end, as follows. Still showing error.

[Oleg]

Hi @Harshamaddur, try removing the quote character before the year as you currently have 3 quotes in your formula, not two or four. The remaining two quotes seem valid to me.

Let us know what kind of result you get.

[Harshamaddur]

I used the following formulae for "Today's row, upcoming row and previous row" respectively. at 12:05, July 29th IST

=FILTER(Workorders,"Workorders[Date]=DATE(YEAR(NOW()+5.5/24),MONTH(NOW()+5.5/24),DAY(NOW()+5.5/24))")

=FILTER(Workorders,"Workorders[Date]>DATE(YEAR(NOW()+5.5/24),MONTH(NOW()+5.5/24),DAY(NOW()+5.5/24))")

=FILTER(Workorders,"Workorders[Date]<DATE(YEAR(NOW()+5.5/24),MONTH(NOW()+5.5/24),DAY(NOW()+5.5/24))")

Dates in work order sheet are 28th, 29th & 30th July. In Summary sheet it showing no of workorders (in workorders) column as per UTC date. Not as per IST date.

Even though these formulae not showing error, they are not serving the purpose.

To explain more: As dates work orders sheet are 28, 29 & 30th. In summary sheet Today, upcoming & previous should show one work order each.