Importing date information> converting to actual DATE for

feeel

New Member
Joined
Feb 18, 2005
Messages
11
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!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

cherria

Well-known Member
Joined
Nov 17, 2004
Messages
708
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

New Member
Joined
Feb 18, 2005
Messages
11
Just tried that and it gave the whole #Value#? thing.... any other suggestions?
 

feeel

New Member
Joined
Feb 18, 2005
Messages
11
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

Well-known Member
Joined
Nov 17, 2004
Messages
708
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top