Check status if updated daily with conditional formatting

[jaryszek]

Hi Guys,

i want to check daily if status was updated.

So daily time to update your status assume is 02.03.2022 06:00 (every day at 6.00 am you should update your status).

So what i want to check in formula (conditional formatting as RED) if i am up to date with my status.

I think it should be something like:
if status date < now < status date +1 but i am not sure.

Can you please help with formula?

Please find workbook attached.

Best,
Luke

p.s. admin please move to formula section, i made mistake and published to wrong branch.

[AliGW]

Thread moved.

CF rule for A1:

=(TODAY()+0.25)-A1>1

[etaf]

EDIt - IGNORE already answered , while i was composing


just to clarify
you are looking to highlight any date that is less than today's date and 6am

=TODAY()+TIME(6,0,0)

use in conditional formatting
=AND(A1 < > "",A1 < TODAY()+TIME(6,0,0))

if not then in your sample please give examples of expected results

[jaryszek]

thank you!

=(TODAY()+0.25)-A1>1

what is 0,25 here? How it is working?

Best,
Jacek

[nick.williams]

Excel stores dates and times numerically. 1 day is 1, so 0.25 is 6 hours, 0.5 is 12 hours, etc. So 0.25 is the same as TIME(6,0,0) as per the solution from etaf above.

[AliGW]

I see someone has answered for me, but just to add my own words, 0.25 is a quarter of 1 (i.e. a quarter of a day), so six hours.

[jaryszek]

Thank you,

Best,
Jacek

[AliGW]

You're welcome.

[jaryszek]

Hi,

i have to reopen topic.

i have date:
"3.7.22 5:34 AM"

and formula i am using:

Please Login or Register  to view this content.

so it is exact time, 6 am.I would like instead of exact time have here the range, so 6 Am +/- 2 hours.

How can i write this?
Sorry i am not good in time formulas...

Best,
Jacek

[etaf]

=AND(A1>=TODAY()+TIME(4,0,0),A1<=TODAY()+TIME(8,0,0))

6 Am +/- 2 hours.
is
4AM to 8Am

But the time has to be between those 4 hours
if its updated at 9:00 it will fail

=A1>=TODAY()+TIME(6;0;0)
means if it was updated anytime after 6am is fine

[jaryszek]

thank you!!

Jacek

[etaf]

you are welcome