GSheets - Time between date and now()

Question

Trying different things and combos, but can't get it right. Times and dates always get me. I'm simply trying to compare now() and a date/time and show "in 2 days 6 hrs 30 mins" or "3 days 1 hr 20 mins ago" and I'm guessing someone has done this already? So, let's say the date in column A is 2023-01-24 10:00 and now it is 2023-01-25 11:05, then Col B should say "1 day 1 hr 5 mins ago"

I've tried duration(), date/time formatting the cell, days(..), but I can't find something that works reliably.

Answer 1

try:

=INDEX(IF(ISDATE_STRICT(A2:A), TRIM(FLATTEN(QUERY(TRANSPOSE(
 IFERROR(LAMBDA(a, LAMBDA(x, IF(x=0,,IF(x>1, x&a&"s", x&a)))
 (DATEDIF(A2:A, NOW(), {"Y", "YM", "MD"})))({" year", " month", " day"}))),,9^9))), ))

Answer 2

Another thing you could try:

=INT(NOW()-A1)&" d "&TEXT(NOW()-A1,"h \hr m \min a\go")

Answer 3

please try:

=TRIM(LAMBDA(y,IF(NOW()-A1<1,"0 days",IF(y=1,y&" "&"day",y&" "&"days")))(DATEDIF(A1,NOW(),"D"))&" "&
 LAMBDA(y,IF(y=1,y&" "&"hr",y&" "&"hrs"))(HOUR(NOW()-A1))&" "&
 LAMBDA(y,IF(y=1,y&" "&"min",y&" "&"mins"))(MINUTE(NOW()-A1))&" ago")

Answer 4

=NOW()-A1 will actually calculate the difference between Now and that time, BUT consider that the time is updated only when you make a change, it's not a countdown second by second. You can add an updater per minute but not more than that. Go to File - Settings and set On change and every minute:

If you need a specific format like that you mentioned you should then do calculations to add them. If you only need days, hours, minutes and seconds, you can do the next checkings and concatenations:

=LAMBDA(dif,
  IF(INT(dif),INT(dif)&" days ","")
  &IF(HOUR(dif),HOUR(dif)&" hours ")
  &IF(MINUTE(dif),MINUTE(dif)&" minutes ")
  &IF(SECOND(dif),SECOND(dif)&" seconds")&" ago")
(Now()-A1)