Importing date information> converting to actual DATE for

[feeel]

Hi all!

Having imported date information from a database, it appears as dd.mm.yyyy e.g. 10.04.2005 in my excel file.

I need to calculate and display the date a week in advance of this so i can schedule a production date....

This is what i need....


ColA 29.04.2005 ColB 20/04/2005

im really struggling...

have checked out other forums and have tried adapting other code an im pretty useless!

 

[cherria]

If your date were in J35 (Just happened to be where I tested it then this gives yuo your date + 7 days

=DATE(RIGHT(J35,4),MID(J35,4,2),LEFT(J35,2))+7

 

[feeel]

Just tried that and it gave the whole #Value#? thing.... any other suggestions?

 

[feeel]

Whooops!!! My apologies... works perfectly!!!

Can u explain to me how it works though? i hate to just take your help an not actually learn anything!

Cheers!!!

 

[cherria]

=DATE takes 3 values year, month and day to create a date that excel understands. Excel holds dates as a serial number being the number of days since 1.1.1900 so by using =DATE and adding 7 to the result, you are simply adding 7 dys toa serial number.

Now LEFT, RIGHT and MID are string manipulation functions that pull out characters from a string of characters.

so your 10.04.2005 is a string of 10 characters.
RIGHT(J35,4) gives me the rightmost 4 characters i.e. the year
MID(J35,4,2) gives me 2 charcters from the middle of the string starting in position 4 i.e. the month
LEFT(J35,2) gives me the 2 characters to the left of the string i.e. the day

By the way I was assuming it was a european date format day.month.year. If it was a US date format month.day.year then you need to reverse the LEFT and the MID within the =DATE formula