Adding dates to get a date
 

I am trying to add two cells plus 30 and get a date. How would I go
about doing this? I have tried everything I know.

for example Q7-M7 or Q7-M7+30

Q7 and M7 are both in date format.

I know that I am missing something really simple, but I can't figure
out what it is.


Someone please help - I'm pulling my hair out!!!!

Thanks
Heather Smith

Heather - It really isn't clear what you are trying to accomplish.
Perhaps some examples with actual dates and results would help us
understand.

Without that, I'll try to take a crack at some concepts.

First off, the date "format" for cells Q7 and M7 is just a format. The
underlying value is a serial number. For instance, today's date
(08/12/2005) in serial format is 38576. 8/13/2005 is 38577 so each
digit is equal to one day. Subtracting one date from another gives the
number of days between the two but adding two dates doesn't make much
sense.

If you add, 8/12/2005 and 8/1/2005 the result will be 3/15/2111 since
what you are really adding are 38,565+38,576 which equals 77,141 which
when formated as a date is 3/15/2111. Add 30 days to that and you'll
get 4/14/2111. I can't imagine why you'd want to do that.

On the other hand, if you want to add 30 days to the elapsed time
between two dates that might make more sense. Using the serial numbers
for 8/12 and 8/1 for purposes of illustration, 38576 - 38565 = 11 or
the elapsed days. Since these are elapsed days NOT a date, you would
format them as a number not a date. Add 30 days to that and you'll get
a total of 41 days.

I know this doesn't answer your question but it should give you some
clues on working with dates. Give some more specifics in your example
and I'm sure we can help.

- John

Heather - It really isn't clear what you are trying to accomplish.
Perhaps some examples with actual dates and results would help us
understand.

Without that, I'll try to take a crack at some concepts.

First off, the date "format" for cells Q7 and M7 is just a format. The
underlying value is a serial number. For instance, today's date
(08/12/2005) in serial format is 38576. 8/13/2005 is 38577 so each
digit is equal to one day. Subtracting one date from another gives the
number of days between the two but adding two dates doesn't make much
sense.

If you add, 8/12/2005 and 8/1/2005 the result will be 3/15/2111 since
what you are really adding are 38,565+38,576 which equals 77,141 which
when formated as a date is 3/15/2111. Add 30 days to that and you'll
get 4/14/2111. I can't imagine why you'd want to do that.

On the other hand, if you want to add 30 days to the elapsed time
between two dates that might make more sense. Using the serial numbers
for 8/12 and 8/1 for purposes of illustration, 38576 - 38565 = 11 or
the elapsed days. Since these are elapsed days NOT a date, you would
format them as a number not a date. Add 30 days to that and you'll get
a total of 41 days.

I know this doesn't answer your question but it should give you some
clues on working with dates. Give some more specifics in your example
and I'm sure we can help.

- John

On 12 Aug 2005 07:21:38 -0700, wrote:

I am trying to add two cells plus 30 and get a date. How would I go
about doing this? I have tried everything I know.

for example Q7-M7 or Q7-M7+30

Q7 and M7 are both in date format.

I know that I am missing something really simple, but I can't figure
out what it is.


Someone please help - I'm pulling my hair out!!!!

Thanks
Heather Smith

I'm missing something, or not sure exactly what you want to do.

Excel stores dates as serial numbers with 1=1/1/1900 or 0=1/1/1904 depending on
the system selected.

If Q7 is a date, and M7 is a date, subtracting one from the other will result
in a number equal to the number of days between those two dates. Adding 30 to
that does not give a date; if you format it as a date it will give you a date
value probably back in the 1900's.

e.g.

Q7 := 31 Aug 2005 = 38595
M7 := 31 Jul 2005 = 38564

Q7-M7 = 31 (formatted as a date = 31 Jan 1900)

Q7-M7+30 = 61 (formatted as a date = 1 Mar 1900)

Perhaps if you explain more clearly what it is you are trying to do, and what
your inputs and expected results are, more help will be forthcoming.



--ron

Ok - thanks for the try guys but here's what I need:

Q7 = 8/15/05 (date format)
M7 = 7/2/05 (date format)

I would like to subtract M7 from Q7 and get a date, not a number - for
example -

Q7-M7 - OH Boy, I just realized my mistake...I guess sometimes it helps
to talk it out like this.

However, my second question is this:

Q7=6/7/05
M7=1/17/05


so what I have in P7 is (Q7-M7) which gives a number of days, and that
is what I want.

(P7 = 141)

But in R7, I have (p7+30) this also gives me a number of days. What I
want here is a date.

Right now I have a number - 141+30=171


Ahhh - right now I see my problem. I can't add two dates together like
that it's impossible.

Thanks guys for the great advice :)

I figured it out!!!!!!!
Heather