Character location

[hayden]

Hello everyone!
I am sure that this has been covered before as i know I have seen it in the past, I just can't find the right combination of words to search for.

I am trying to create a column of numbers that are made up of the year,month,day. This is easy with dates prior to 1900 however with dates after 1900 you need an add-in Xdate. I downloaded this addin but it doesn't quite work.

the result of =xdatemonth(03) is 3 and not 03. I need it to keep that 0. Same with the day ie 01 should stay 01 and not revert to 1.

So I was going to use an offset (i believe it is an offset) to parse out my current date into the that format.

Example.

date is 03/31/1887 so the new column would contian 18870331. ie. first the characters 7-10 then characters 1-2, then characters 4-5.

I tried to do a simple find and replace and remove the / but my dates are formatted in 03/31/1887 and they don't read as dates so I can't format them as 1887/03/31

Any help is greatly appreciated.

Hayden

 

[hayden]

Ah ha! What about this...

I calculated the year of the date first.

ie xdateyear(1887/03/31) = 1887
Then i added 100 to all of my dates so that 1887 became 1987 and 1987 became 2087.

now I took that year and used it to recreate my serial number date with

year(1987)&xdatemonth(1887/03/31)&xdateday(1887/03/31)

Now I have a number that I can use in an if statement such as is less than 1987/03/31 and just use 100 more on all my dates

 

[tusharm]

My assumption is that the XDate add-in treats dates *prior* to 1900 as text since it would be the only way to make it work. A few options come to mind.

First, check if the addin supports different formats. If so, just specify yyyymmdd instead of mm/dd/yyyy, which I suspect is also the default.

Second, since the date is actually a text value, you can reorganize it yourself with the untested =right(A1, 4) & left(A1,2) & mid(A1,4,2)

The last option I will suggest (FWIW, others do come to mind) would be to take the output of XDateMonth and ensure it is 2 characters long with something like the untested TEXT(XDateMonth(A1),"00"). So, you would use -- and this assumes the existence of the XDateDay and XDateYear functions --
=XDateYear(A1) & TEXT(XDateMonth(A1),"00") & TEXT(XDateDay(A1),"00").

To convert the result to a number coerce a text to number conversion with a dummy arithmetic operation as in
=0+(XDateYear(A1) & TEXT(XDateMonth(A1),"00") & TEXT(XDateDay(A1),"00"))

 

[RalphA]

In A1, enter 03/31/1887 and format as text.
In B1, enter =RIGHT(A1,4)&LEFT(A1,2)&MID(A1,4,2)
You should see, in B1, 19970331

In A1, enter 03/31/2005 and format as text.
In B1, enter the above formula.
You should see, in B1, 20050331

 

[tusharm]

Yes, that should work and it reminded me that the first option I should have considered was something along those lines. To get the date to be a number like yyyymmdd use XDateYear(A1)*10000+XDateMonth(A1)*100+XDateDay(A1)

Ah ha! What about this...

I calculated the year of the date first.

ie xdateyear(1887/03/31) = 1887
Then i added 100 to all of my dates so that 1887 became 1987 and 1987 became 2087.

now I took that year and used it to recreate my serial number date with

year(1987)&xdatemonth(1887/03/31)&xdateday(1887/03/31)

Now I have a number that I can use in an if statement such as is less than 1987/03/31 and just use 100 more on all my dates

 

[Scott Huish]

How about:

=xdateadd(A1,0,"yyyymmdd")


Here is the link to this add-in:
http://j-walk.com/ss/excel/files/xdate.htm