So for some reason it only works if you create extra column with date 
and then do filtering
1 Like
It looks like you're missing a quote (") in your B2 equation between the two closing parens at the very end.
1 Like
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.
2 Likes
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.
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]<>""""")
2 Likes
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.
1 Like
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
1 Like
lol Glad I was able to help
2 Likes
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.
This expression =DATE(YEAR(NOW()+(5.5/24)),MONTH(NOW()+(5.5/24)),DAY(NOW()+(5.5/24))) should only return the date, but it will show time at midnight if you format the column as a "Date & Time". Essentually forcing it to pick 00:00 or 12:00.
=(TODAY()+(5.5/24)) should return todays date respecting the IST timezone, although this will not return the current time. For that, I the formula would be =(NOW()+(5.5/24)) and format the column as "Date & Time"
Hope this helps!