Re: Calculatingelapsed days

Marv wrote:
> If I have cell A2 as 12-31-2005  and cell A as 12-19-2006 how can I 
> determine the number  of days between them. I have tried playing with date

> diff but can't seem to get it to work.

Just subtract the two dates, and apply the appropriate adjustment for what you 
actually want "number of days between" to mean:

   +A2-A1-1   days which fall in between these two dates (i.e., both
exclusively)
   +A2-A1     days elapsed since the start date, as of end date
   +A2-A1+1   days from start to end date, (i.e., both inclusively)

Cheers,
Uli

If I have cell A2 as 12-31-2005  and cell A as 12-19-2006 how can I 
determine the number  of days between them. I have tried playing with date 
diff but can't seem to get it to work.

Thanks, Marv 

"Uli" <nojunkmail@spamless.com> wrote in message
news:47671000_2@cnews...
>
> Marv wrote:
>> If I have cell A2 as 12-31-2005  and cell A as 12-19-2006 how can I 
>> determine the number  of days between them. I have tried playing with 
>> date diff but can't seem to get it to work.
>
> Just subtract the two dates, and apply the appropriate adjustment for what

> you actually want "number of days between" to mean:
>
>   +A2-A1-1   days which fall in between these two dates (i.e., both 
> exclusively)
>   +A2-A1     days elapsed since the start date, as of end date
>   +A2-A1+1   days from start to end date, (i.e., both inclusively)
>
> Cheers,
> Uli
>


Uli, thanks for the response.  I am obviously missing something. In what 
format should the dates  in  the two cells be  entered.  If I precede the 
data with a single  quote ('12-31-2005 and  '12-19-2006) then I get no 
result. If I leave out the single  quote and enter (12-31-2005 & 
12-19-2006 ) then the program changes it to -2024 and -2013 which results 
in a value of 11. I get the same results regardless of  which date is in 
which cell and if I reverse the + & - of the formula.  I am expecting a 
value of about 353.

I did just notice that I had left off the number of  the second cell in my 
original post.  The two cells  and their  value should have been   A2 = 
12-20-2005, A3= 12-19-2006.

As you can see I am a very infrequently user of spread sheets.

Marv wrote:
> Uli, thanks for the response.  I am obviously missing something. In what 
> format should the dates  in  the two cells be  entered.  If I precede the 
> data with a single  quote ('12-31-2005 and  '12-19-2006) then I get no 
> result.

Entering ANYTHING with a leading single quote amounts to entering TEXT data (as

opposed to numerical data, including dates).  The VALUE of any text is zero, 
regardless of its APPEARANCE, and is thus unsuitable for any kind of date 
arithmetic.

> If I leave out the single  quote and enter (12-31-2005 & 
> 12-19-2006 ) then the program changes it to -2024 and -2013 which results 
> in a value of 11. I get the same results regardless of  which date is in 
> which cell and if I reverse the + & - of the formula.  I am expecting a

> value of about 353.

QP interprets this type of "date" entry as "12 minus 31 minus
2005", which 
equals -2024 (as you discovered).

QP only allows a handful of formats in which to ENTER a date into a cell.  (It's

important to distinguish between date ENTRY format and date DISPLAY format, 
which are independent of each other).  For dates later than 1950, the best date

entry format is:

   DD-MMM-YY (28-Apr-97)

For dates prior to 1951, you need to be slightly more specific:

   DD-MMM-YYYY (28-Apr-1939)

There are a few other date entry formats that work, but the two format above are

the most reliable in terms of man/machine interpretation, because they are 
independent of system configuration and are unambiguous for both the user and 
the program as to what is the day and what is the month.

A properly entered date will have an underlying numerical value equal to the 
number of days since 31-Dec-1899 (Day 1), which results in a numerical value of

39433 for today's date, which can be formatted to be DISPLAYED in any available

date format.

> I did just notice that I had left off the number of  the second cell in my

> original post.  The two cells  and their  value should have been   A2 = 
> 12-20-2005, A3= 12-19-2006.

Then +A3-A2, i.e., the later date minus the earlier date.

Cheers,
Uli

"Uli" <nojunkmail@spamless.com> wrote in message
news:4767484d_1@cnews...
>
> Marv wrote:
>> Uli, thanks for the response.  I am obviously missing something. In
what 
>> format should the dates  in  the two cells be  entered.  If I precede
the 
>> data with a single  quote ('12-31-2005 and  '12-19-2006) then I get no

>> result.
>
> Entering ANYTHING with a leading single quote amounts to entering TEXT 
> data (as opposed to numerical data, including dates).  The VALUE of any 
> text is zero, regardless of its APPEARANCE, and is thus unsuitable for any

> kind of date arithmetic.
>
>> If I leave out the single  quote and enter (12-31-2005 & 12-19-2006
) 
>> then the program changes it to -2024 and -2013 which results in a value

>> of 11. I get the same results regardless of  which date is in which
cell 
>> and if I reverse the + & - of the formula.  I am expecting a value
of 
>> about 353.
>
> QP interprets this type of "date" entry as "12 minus 31
minus 2005", which 
> equals -2024 (as you discovered).
>
> QP only allows a handful of formats in which to ENTER a date into a cell. 
> (It's important to distinguish between date ENTRY format and date DISPLAY 
> format, which are independent of each other).  For dates later than 1950, 
> the best date entry format is:
>
>   DD-MMM-YY (28-Apr-97)
>
> For dates prior to 1951, you need to be slightly more specific:
>
>   DD-MMM-YYYY (28-Apr-1939)
>
> There are a few other date entry formats that work, but the two format 
> above are the most reliable in terms of man/machine interpretation, 
> because they are independent of system configuration and are unambiguous 
> for both the user and the program as to what is the day and what is the 
> month.
>
> A properly entered date will have an underlying numerical value equal to 
> the number of days since 31-Dec-1899 (Day 1), which results in a numerical

> value of 39433 for today's date, which can be formatted to be DISPLAYED in

> any available date format.
>
>> I did just notice that I had left off the number of  the second cell in

>> my original post.  The two cells  and their  value should have been  
A2 
>> = 12-20-2005, A3= 12-19-2006.
>
> Then +A3-A2, i.e., the later date minus the earlier date.
>
> Cheers,
> Uli
>


Uli, I know this can't be as complicated as I make it but I am getting 
results that I do not understand.  If I have the following data entered in 
the format you indicated:

   Enter A1      Displays   Results of A2-A1
12-31-05         -24           11
12-09-06         -13         364
12-19-06         -13             1
10-01-07         -2             15

The first three are as I expected, but how can I get the 15 in the fourth 
entry?

In another enter of:
01-03-05          -3              5
10-01-07           2

How did it arrive at 5?  Should the calculation of number of days since 
31-Dec-1899 be displayed by the program?  Now I remember why I haven't used 
spreadsheets very much.  I have never taken the time needed to understand 
them.

Marv