date subtraction difficulty

[Peter]

I'm trying to subtract dates, to calculate time duration. I'm
subtracting column A from column B into column C. Column C is
formatted 'm "mos", dd "days"' and columns A and B are formatted
'mm/dd/yy/@' Unfortunately each of the durations is 1 month too
long. That is, if the real duration is 4 days, column C will
display '1 mos, 4 days'

Any clue what I'm doing here?

Thanks much.

 

[Ron Rosenfeld]

I'm trying to subtract dates, to calculate time duration. I'm
subtracting column A from column B into column C. Column C is
formatted 'm "mos", dd "days"' and columns A and B are formatted
'mm/dd/yy/@' Unfortunately each of the durations is 1 month too
long. That is, if the real duration is 4 days, column C will
display '1 mos, 4 days'

Any clue what I'm doing here?

Thanks much.

If you are formatting the target cell as a date, Excel will display it as a
date!

So if B1-A1 = 4, Excel interprets that as a date, doing just what you told it
to do.

Day 4 in Excel is 4 January 1900

Since the "m" in your format code means to display the numeric equivalent of
the month of the year, and since the month of the year is January, Excel will
display a "1" for your "m" token.

The difference between two dates is NOT a date; rather it is a number.

So for the number of Days, you could format as 0" days"

For days and months, you will need to decide what you mean by "month" since a
month can be 28, 29, 30 or 31 days long.


--ron