I have a spreadsheet set to calculate the “days” between battery changes but I would like to have “days, hours and minutes. I’m currently using “=IF(ISNUMBER(J10),DAYS(NOW(),J10),””)". I’ve changed the format code from “NN D MMM YY” to “NN D MMM YY HH:MM” to try to achieve this but when I try to combine “HOUR(NOW())” and “MINUTE(NOW())” into “=IF(ISNUMBER(J10),DAYS(NOW(),J10),”)" I get errors.
Is there a way I can do this?
May be this could serve as a hint:
=INT(NOW()-J10) & "d, " & HOUR(NOW()-J10) & "h, " & MINUTE(NOW()-J10) & "m"
but might be you want:
=INT(J10-H10) & "d, " & HOUR(J10-H10) & "h, " & MINUTE(J10-H10) & "m" in cell I10
(not sure what you really try to calculate)
The subject you chose is misleading. The NOW() function is an arbitrary ingredient of your formulas and of what you want to achieve.
The actual question was: How to output a duration using day, hour, and minute as descending units?
Might you edit the subject, and use a wording similar to what I suggested?
Thanks for your replies. I’m not sure at all about the “INT” function. I need to learn more about where I could use it.
Lupp, I’ve read some of your answers, over the years and I’ve learnt from them but without delving too much (yet) into this one, while appreciated, I find it a bit more confusing on this occasion.
Opaque, your answer was more than a hint as it worked ‘straight out of the box’. I modified it slightly to suit my purposes, so I’ve used “=IF(ISNUMBER(J11),INT(NOW()-J11) & “d, " & HOUR(NOW()-J11) & “:” & MINUTE(NOW()-J11),””)".
Your second formula certainly shows up my error in column “I”. I’ve changed that from “=IF(ISNUMBER(H11),DAYS(NOW(),H11),”")" to “=IF(AND(ISNUMBER(H10),ISNUMBER(J10)),J10-H10,IF(ISNUMBER(H10),DAYS(NOW(),H10),”"))". I’ll need to do the same with the formulae in column “K” later.
Once again, thanks for your replies.
Off to look at “INT” now.
Functionally INT(NOW()) is the same as TODAY(). Used stand-allone in a cell it avoids the automatic (often spoiling/misleading) assignment of a date format to the cell by TODAY().
What you actually need to understand is how date-time-stamps are handled in spreadsheets: The integer part contains the calendaric day as the number of days snce 1899-12-30 (taken as day zero). The fractional part codes for the TimeOfDay using the pseudo-unit day (d) for it. I call it a pseudo-unit because days may differ in lentgh: In rare cases by 1 s due to a leap second mandated by international authority for very good reasons, in other cases by 1 h due to nonsensical regulations by statal authorities.
I would love to know in what way my answer was confusing, and by what examples you found it was not working ‘straight out of the box’. If you find the time to tell me, please do so by adding a comment on the answer.
…sorry Lupp, confusing for me. I need to take time to get my head round it. Time I don’t have today. I’m always willing to learn about spreadsheets so I will come back to it when I have the time. Thanks again and keep up the good work.
Using “Y”, “M”, “D” as formatting code letters you always get it interpreted the calendaric way, and never concerning durations. It’s simply useless in your case.
As long as only the date of insertion is entered, a calculation of the duration with higher resolution than a day will mostly be nonsense.
If you made sure to have a reasonable date-time-stamp as a number in cell a2, you can display the duration (up to now) as a text by =TEXT(INT(NOW()-A2);"0"" d "" ") & TEXT(NOW()-A2-INT(CURRENT());"HH"" h ""MM ""min""").
Of course, it’s better to supply the duration (as an ordinary difference e.g.) in an extra cell, say B2.
This will anyway be necessary because it’s really complicated to get the duration back from the formatted string as a number you can calculate with.
The adapted formula would then be =TEXT(INT(B2);"0"" d "" ") & TEXT(B2-INT(B2);"HH"" h ""MM ""min""")
Please note: Always disambiguate literal parts of format strings. Using the TEXT() function this requires to insert the functional doublequotes as doubledoublequotes because they occur inside a text constant itself being enclosed by doublequotes.