Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
|
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
Finding dates within a date range | Excel Worksheet Functions | |||
Finding Dates in a date range | Excel Discussion (Misc queries) | |||
Using a col of Dates by day I want to determine a wk ending date. | Excel Worksheet Functions |