Calculating TIMEDATE 2 hours prior

[DelproServices]

Hey All,

New to app sheets and just learning about Format Rules, i am in need of assistance please. i will do my best to explain my issue.

I have a row in my table called [SLA] which is manually input by a user to display when a job is needed to be completed by, it’s type is DateTime.

Is it possible to write a YES/NO Condition that will display a colour that only shows if:

1. The current TIMENOW() is equal to or less then 2hours of the [SLA]
2. the current TIMENOW() is greater then the [SLA]

I hope that makes sense if you need any more information or pictures please let me know.

Thankyou.

[Suvrutt_Gurjar]

1. The current TIMENOW() is equal to or less then 2hours of the [SLA]
   [SLA] >= (NOW() - “002:00:00”)`

2. the current TIMENOW() is greater then the [SLA] ( I believe you are looking for TIMENOW() is more than 2 hours since [SLA} was enterd.

   [SLA] < (NOW() - “002:00:00”)

I believe you may use NOW() instead of TIMENOW(0 just in case your SLAs have rollover between different dates. For example between 11 PM on 12th October to 1 AM on 13th October.

However I believe these time related format rules will take place or refresh only when the user manually refreshes the app or app syncs due to some other add/delete/edit operation on the app.

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

[DelproServices]

Hey Survrutt,

Thanks for your swift reply, i was kinda on the right track but now its making more sense to me. So i have tried [SLA] < (NOW() - “002:00:00” which works but i changed it to [SLA] < (NOW()) now it shows all the jobs that have now past their expiry date of attendance which is perfect colour=red.

As for the second part i can get the jobs showing yellow if they are within 2 hours of the [SLA] but its also showing other dates & times in the future that are not required. Is it possible to only show the dates & times that are within 2 hours of NOW() & [SLA]. I will upload photos of both examples so it makes a bit more sense Colour=Yellow.

Thanks Nick.

[Suvrutt_Gurjar]

Hi @DelproServices

Please try

AND( [SLA] >= (NOW() - “002:00:00”), [SLA] <NOW())

[DelproServices · Accepted solution]

Hey Survurtt,

So i tried it and it did not work so i messed around with different combinations and managed to find the one that works for me AND([SLA] <= (NOW() + “002:00:00”), [SLA] >NOW()).

I really appreciate your help it has made my application that one step better and further towards my goal.

Thanks Nick.

[Suvrutt_Gurjar]

You are welcome and all the best with your app. Nice to know that you got it working per your requirement.