Finding the difference between two dates with time and showing negative numbers

See if this works for you:

Formula:

Please Login or Register  to view this content.

Are you required to use Excel? Are you required to output the elaborate text string?

I'm not sure why Excel has not, yet, "fixed" the "cannot display negative time" bug, but other spreadsheets have long been able to display negative times. If I open your file in LO Calc, enter the simple formula =B3-A3 into D3 (copy/paste/fill down) and format as elapsed hours [hh]:mm:ss, I get 02:12:12 in D3 and -02:12:13 in D4. If you do not have to use Excel and can tolerate a basic elapsed hours display, this can be really easy in other spreadsheets.

If I had to use Excel and output that exact text string and have the information for some kind of future reference, here's how I would probably do it:

1) In one column, calculate the basic difference between the two time stamps. =B3-C3 (format as general, if Excel automatically applies a different number format). I don't know what kind of future reference you need, but I would want this column so that, if I ever needed to reference the difference as a number that is meaningful to the spreadsheet, this column will have that number. For my testing, I put this formula into D3 (copy/paste/fill down).
2a) In a convenient cell (I used E1), enter the text string d" days "h" hours "m" minutes "s" seconds"
2b) In a second column (I used E3), generate the text string with something like =IF(D3>=0,"+","-")&TEXT(ABS(D3),$E$1) or something similar to the formulas given earlier in the thread.

A lot depends on exactly what I might need for "future reference," but I would do it in 2 columns like this so that I have both the numeric "serial number" representation of the time and the text representation. The numeric representation will be useful if my "future reference" includes any needs for numeric analysis (like averaging or ranking or whatever edit: or unit conversion), and the text representation meets the requirement to have that specific text representation for users to read.

Does that help?

Unless I am missing something, I think this works (note that the file in post #8 had so many protected cells, that I chose the file in post #1 as my starting point):

1) In D3, I enter =B3-A3 (format as general).
2) In E1, I enter h" hours "m" minutes "s" seconds "
3) In E3, I enter =TRUNC(D3)&" days "&TEXT(MOD(ABS(D3),1),$E$1). If I understand some of the question, I think the difference between INT() and TRUNC() is important to understand and use (see Excel's help files for documentation of the difference: https://support.microsoft.com/en-us/...4-aa5ceb292721 )
4) In F3, I enter =CONVERT(D3,"day","sec") to convert the difference from decimal days to decimal seconds (based on the other thread that asked about converting to seconds). Select D3:F3 and copy/paste/fill down.

I then entered a formula like =RANDBETWEEN(45000,45005)+RAND() into A6 (copy/paste/fill into A6:B25). Copy D3:E3 to row 25. Spot check results and it seems to work for days that are up to 6 days apart.

Is that getting closer to what you want?

You didn't tell us where to look... so a guess:

="Video is "&IF(B4>C4,"- ","+ ")&INT(MAX(B4:C4)-MIN(B4:C4))&" days "&TEXT(MAX(B4:C4)-MIN(B4:C4),"h"" hours ""m"" minutes ""s"" seconds""")

yellow shaded cells.

In I9, you suggest that it is necessary to convert the time difference to a complex text string in order to convert to seconds. I'm not sure why that is necessary. It seems to me that =CONVERT(C10-B10,"day","sec") should be all that is needed to convert to seconds. What am I missing?

IMO, text strings are the hardest way to deal with numbers, and time stamps and time differences are just numbers (behind the number formatting). The text formatting is fine as a final output value, but I would not use it as an intermediate calculation.

Ultimately I want to be able to put two different dates and times no matter how many days in between and have it spit out a text that says what I have (signed difference between those two date/time stamps?).

As I noted in post #6 (and considering other things you are wanting to do, I would stick with that recommendation), I would have a column that just calculates the signed difference (=C10-B10 formatted as general) in decimal days. Then when I get to

I can reference the new date and everytime I put a new time on another sheet, it will automatically add the new time with the converted seconds.

I can simply put =new date/time+(cell with C10-B10). As near as I can tell, that sequence of calculations should be no more complicated than that.

Then, you can use your "convert signed difference to text string" formulas to display the C10-B10 difference in a meaningful way (since most users are not going to know what to do with "difference is -0.091817 days"). Because you've stored the numeric meaning of that text string elsewhere, there will be no need to convert that text string back into a number.

I've illustrated this in columns M:O of the attached. Columns M:O can do the "take the difference then add it to a new date/time stamp" steps, while your original columns can handle the "convert signed difference into a text string that is meaningful for the reader" task.

Would it be okay to divide up responsibilities like that?