How can I (easily) format long intervals (over 30 days, but including time components)

user_9305821 0
Viewed 60

I am using a spreadsheet to record the intervals between certain medical events. Each event's timestamp is recorded in one column, so the interval between each event is the difference between consecutive cells (and the time since the last event uses now()).

First problem: I want to display the interval in days, hours and minutes. None of the built-in formats will do this, they report the days remaining after discarding complete months. So I am using this expression:

TEXT(TRUNC(C2-C1),"0") & " days " & TEXT(MOD(C2-C1,1),"hh "" hrs "" mm ""mins""")

which (e.g.) shows "46 days 13 hrs 44 mins". I was hoping there was a way to format a date/time value to show this rather than making the cell a string value, but I haven't been able to find one.

Second problem: I want to display the average value of all completed intervals in the same format. Because I can't average the string values produced by the previous expression I need to average the numeric equivalent (which I'd prefer not to have visible in the sheet) and then convert it to a string as for a single interval.

I can probably do this with a similar approach (if I don't run out of characters to enter the formula) but it seems to me that there must be a better way.

Ideally there is a solution which will work in Excel 2010. Has anyone solved a similar problem before and can give me some pointers?

Thanks, T

Edit: Some data to show what I am working with (I hope the image is readable). Here's a few lines from the sheet.

Raw (unformatted) data

The formula for H2 etc. is

=IF(G2="c",NOW()-C2,"")

I5 is calculated as the difference between C5 and Cprev (where prev is chosen so that D5 and Dprev are both set). Obvious extension of this for J and K.

M2, M3 and M4 are respectively

=AVERAGEIF(K5:(INDIRECT("K"&(ROWS(K:K)))),"<>0")
=AVERAGEIF(J5:(INDIRECT("J"&(ROWS(J:J)))),"<>0")
=AVERAGEIF(I5:(INDIRECT("I"&(ROWS(I:I)))),"<>0")

Now, I can use a custom format for the values in H, I and J, and for M3 and M4, because these values will never exceed a few days. But values in K and M2 will be somewhere around 100 so I can't just format the raw value.

With custom formatting applied: enter image description here

Here K6 and M2 say "27 days.." not "87 days..". That's what I'd like to fix nicely, hopefully without populating additional cells or writing a 3gl function to do it.

excel-2010 intervals excel
1 Answers

Date_Times in Excel are stored as days (with the decimal part representing parts of a day).
If the date matters, rather than just a number of days, then day zero is 1899/12/31.
Your first thought was right - do with formatting, not by turning a number into a string.
Entering date into A and time into B, with C=A+B is a good start, so that you can E.g. subtract one point in time from another without having to do anything about straddling midnight, month-ends etc, calculate averages etc.
Consolidating the comments already here: per https://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309 you cannot get d for days above 31 (and it won't accept a format 0 hh:mm:ss )
I suggest that you do all your calculations using just numbers to get to say column M, and in N2 put =M2 etc, so you have the same values twice.
Then for formatting, use Format Cells | Number | Custom.
In column M put 0 "days".
In column N put hh "hours" mm "minutes".

user_152134840
Related