Solved Excell adding days to dates

December 31, 2012 at 05:53:37
Specs: Windows Vista
I would like to know how to add one week to every column...(I'll explain better below).

column C represents "29th Dec 2012"

I would like column D to represent "5th Jan 2013"...

but I would love to use a formula to achieve this as I am making a weekly rota that needs to last until the end of (next) year.

I have tried typing into D1...=C1 +7 but this isn't working. Both cells are formatted as "dates" but I can't seem to get it to work.

Thanks


See More: Excell adding days to dates

Report •


#1
December 31, 2012 at 06:55:32
✔ Best Answer
You used the words "isn't working".

Keep in mind that we can't see your spreadsheet from where we are sitting, so you need to provide some detail as to what "isn't working" really means.

I am going to assume that you are getting a #VALUE error becasue that is what I get when I try to add 7 to a cell that contains 29th Dec 2012.

The reason for that is that there is no date format that includes th, st, nd or rd. Even if you format the cells as Date, Excel is going to see the cell as Text. Since you can't add a number to Text, Excel returns a #VALUE error.

How hard would it for you to use actual Dates (29 Dec 2012) in your cell? It is possible to convert text-dates with the extra letters to a real date and then add 7 to it with the following formula:

=DATEVALUE(SUBSTITUTE(C1,MID(C1,FIND(" ",C1)-2,2),""))+7

This formula will return January 5, 2012 formatted in whatever Date format you chose for the cell.

The formula will replace the 2 characters found before the first space with "" (nothing). Excel will then use the DATEVALUE function to convert the Text e.g. 29 DEC 2012 to a Date.

Once Excel knows the DATEVALUE of the Text, it will add 7 to it.

Keep in mind that the DATEVALUE function can only "convert" the Text to a Date if the Text is in a format that would be a valid Date format.

e.g. 29th Dec 2012 is not a valid Date format but 29 Dec 2012 is, therefore we need the SUBSTITUTE function to strip off the letters.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 31, 2012 at 07:21:27
Wow! Thank you! Yep it was coming up as #VALUE before and is now sorted! Thanks very much!

Report •

#3
December 31, 2012 at 07:28:16
Your job now is to study the formula and understand exactly why it works so that you can use the techniques in other situations.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 31, 2012 at 07:44:08
Yep, I agree, will do! Thanks!

Report •


Ask Question