Thread: Formulae time difference

Good afternoon,

I have been tearing my hair out trying to construct a time difference formula. I have:

Cell A1 Date format
=NOW()

Cells A3, A4, A5 etc.etc. Date format
10:01
10:02
10:03
etc.
etc.

The spreadsheet refreshes every few seconds and therefore the actual time updates at each refresh.

I have been trying to get a formula such as =if(A1>=A3,TRUE,FALSE) to work - without success.

The times increase at minute intervals from 10:00 to 23:00.

Regards

Hello
Try using the MOD function to extract the decimal time part of the NOW function.

=IF(MOD($A$1,1)>=A3,TRUE,FALSE)

I've made the A1 reference absolute so as to always refer to that cell when you move the formula down. If the NOW function is situated elsewhere you'll have to amend that.

Hope this helps and you have some hair left!

Good morning,

I can't thank you enough for your concise formula which ran without a hitch.

From googling the problem tens of times I got the impression that Microsoft themselves didn't really have a clue.

Hair transplant now in place and is already growing.

Regards

Chris