I have a few time cells that sum more than 24 hours, like these:
For these cells i have used the time format: [HH]:MM:SS. The cells themselves have a function in the back, they are not input.
How can i make so that those big numbers (greater than 24 hours) are shown as years, months, days, hours? And to show only if the number of years, months and days are greater than 0.
Do i have to use a function or a format? Couldn’t find how to format.
Thank you.
Could you please clarify, exactly how many seconds there are in a month?
I made a mistake, thank you. Edited the post.
As text output: =(INT(A2-A1)&" Days "&TEXT(A2-A1;"HH:MM:SS")
For further calculations use A2-A1.
1 Like
What about the number of months and years? Which function?
To mention that the two columns that i posted have no relation to one another, they were just examples of numbers i’m encountering.
How can i make it so that it shows the number of days only if the the number is greater than 0? If it’s 0, i prefer to not appear at all, just the rest of the time in hours, minutes, seconds.
Note that you didn’t answer my question. Which was not about a “mistake” that you edited; but about how to show you duration X as N months + something. So again: exactly how many seconds (or minutes, or whatever strong time unit you prefer) there are in a month? And then, of course, exactly how many seconds are in a year.
MS (you may be aware of them) do not even realize that “difference” is spelled with a double “f”, not to mention that months and years do not even come close to representing a unit of time.
Do not be too harsh on an ordinary user.
Let me clarify: the time format i use is [HH]:MM:SS, like 44:36:25. I would like it to show “1 day 20:36:25”. Another number in the same format, 20:17:43, would be “0 days 20:17:43”, and i want it to show only if the number of days is greater than 0. The same rule to apply for the number of months and years. How can i do that?
It is not the same situation, i want to show the time as duration, not a specific date.