Date Time Alteration by Formula

[nicoan]

Hello All


We have this:
kvxYcOb.png


- In A1, we get a data source update that cell every hour with the next hour. Note that the minutes doesn't matter, if the data is updated, the A1 cell should show the next rounded hour. Kind of a "round up".

-In A2 we just have the formula : =NOW()


We want to build a cell that show the same data as if we had a successful update, exactly the same as A1 but with a formula to use as a confirmation that the data is the latest. Like:
=IF(A1=A2,"DATA UPDATED","WARNING, UPDATE FAILED")


How should be the A2 formula?

[Phuocam]

Try:

=IF(FLOOR(A1,1/24)=FLOOR(NOW(),1/24),"DATA UPDATED","WARNING, UPDATE FAILED")

[nicoan]

Ah I didn't know I could use rounding in dates.
I knew the floor function, but it rounds down. The "Ceiling" was the correct round up function.
I changed it in your formula and it worked.

Thank you very much Phuocam. Appreciated.

[nicoan]

Fair enough.

CEILING is the function that "Rounds Up". (FLOOR is the function that "Rounds Down"). Apparently it could be used in dates too.

The solution then:

In A2:
=CEILING(NOW(),1/24)
The format must match the one in A1.

Finally, the IF statement (in another cell) is the same as in the OP:
=IF(A1=A2,"DATA UPDATED","WARNING, UPDATE FAILED")