TODAY()

Joel-1ada · July 22, 2020, 10:17pm

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

Harshamaddur · July 23, 2020, 5:19am

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

Julsi · July 23, 2020, 1:47pm

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 · July 24, 2020, 12:35am

@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 · July 24, 2020, 11:19am

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

Regards
Harsha

Isaiah · July 24, 2020, 5:45pm

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.

Harshamaddur · July 24, 2020, 9:59pm

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 · July 26, 2020, 4:05pm

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 · July 26, 2020, 4:33pm

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

Harshamaddur · July 26, 2020, 7:15pm

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 · July 26, 2020, 7:28pm

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

Isaiah · July 27, 2020, 6:41pm

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

Harshamaddur · July 27, 2020, 9:24pm

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

Oleg · July 28, 2020, 1:10am

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 · July 28, 2020, 6:44pm

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.

Julsi · July 29, 2020, 8:22pm

Table 1

Table 2

formulas:
B2=FILTER(Table1,"Table1[Date]>Table1[Column2]")
B3=FILTER(Table1,"Table1[Date]<Table1[Column2]")
B4=FILTER(Table1,"Table1[Date]=Table1[Column2]")
B5=FILTER(Table1,"Table1[Date]<>""""")

Oleg · July 30, 2020, 1:42am

Thank you @julsi. Love seeing users engage with other users!

@Harshamaddur I've just tested the approach that @Julsi outlined above and it appears to work. Could you confirm that it works for you as well? If not, I'll follow-up with someone on our sheets team to assist.

Harshamaddur · July 30, 2020, 8:16pm

Julsi,
You did it, you did it. Thank you so much.
It works as needed. It is showing as per IST now. Thanks again. I had to change - to + before (5.5/24) for IST.
I would like to thank others also for the effort they put in.
Regards
Harsha

Julsi · July 30, 2020, 8:30pm

lol Glad I was able to help

Harshamaddur · July 30, 2020, 8:48pm

What is the difference between

=DATE(YEAR(NOW()+(5.5/24)),MONTH(NOW()+(5.5/24)),DAY(NOW()+(5.5/24)))

and

=(TODAY()+(5.5/24))

I tried using

=(TODAY()+(5.5/24))

in column 2 of Table 1. But, it is showing UTC time.