Format a date

[doobybug]

Hi I have an excel sheet and one of the fields is a date written in the format ex: 17th December 2017. This is currently a text field but I want to import it in an access database as a date field. Is there a way I can format this kind of date?

[TMS]

Try:

Please Login or Register  to view this content.

[6StringJazzer]

Add a column using this formula

Please Login or Register  to view this content.

where the date field is in A1, and copy down. This assumes that all dates are in exactly the format you gave, with standard ordinal numbers (ex: 1st, 2nd, 3rd, 4th).

[doobybug]

Unfortunately it is giving me a #value error. This is an example of 3 dates from a list:

25th November 1820
14th March 1821
6th July 1821

[MarvinP]

Or try

=DateValue(SUBSTITUTE(A1,MID(A1,FIND(" ",A1,1)-2,2),"",1))

Try this instead of the above:

=SUBSTITUTE(A1,MID(A1,FIND(" ",A1,1)-2,2),"",1)

The error is because Excel only does dates after Jan 1 1900.

[TMS]

@6SJ: I'll give you that on brevity

[TMS]

@MarvinP: should that not be:

Please Login or Register  to view this content.

Also points for brevity

[doobybug]

@MarvinP...why did you include B1...what should be there in B1? Is it where the formula is going to be written?

[TMS]

Unfortunately it is giving me a #value error. This is an example of 3 dates from a list:

Your dates are pre-1900. The date system starts from 01/01/1900

[doobybug]

Nope still doesn't work

[doobybug]

@TMS so the functions available do not work on pre 1900?

[MarvinP]

See my above post where I edited my answer..

[doobybug]

@MarvinP thanks a lot...there is no way I can work with pre 1900 dates in excel?

[TMS]

Nope still doesn't work

What doesn't. You have three solutions. I suspect that none of them will work.

[AlKey]

Try this
Enter in B1 and copy down
Format as Date

Please Login or Register  to view this content.

v	A	B
1	17th December 2017	12/17/2017
2	1st January 2016	1/1/2016
3	3rd March 2018	3/3/2018

[TMS]

For your edification: http://www.exceluser.com/formulas/earlydates.htm

[doobybug]

I think none are working as they are pre 1900 dates...even the last solution given by AlKey...it is giving me N/A as an error

[TMS]

@AlKey: and more points for brevity. Now I'm getting embarrassed

[MarvinP]

You can "work with them" as text strings but using Excel's DateValue just doesn't work. You could build some type of converter from the text string to your own date converted number, if you tried.

I do some genealogy and have this problem with old dates.

Look at

http://www.exceluser.com/formulas/earlydates.htm

[TMS]

Can't say this a different way. The Excel Date functions do NOT work with pre-1900 dates. Whilst the errors may be different, they just don't work.

[doobybug]

What I am thinking is that maybe I will get a substring of the dates and subdivide them in 3 fields and then concatenate them to form the date...do you think it is a solution?

[TMS]

All the formulae produce a Text string that looks like a date. It's the DateValue part that doesn't work. So, you can have a Text string and then use Text to Columns to split it up. However, in order to sort the three columns effectively, you would need to convert the month name to a number.

[MarvinP]

AND - here is a page that shows the code to do that..

https://www.extendoffice.com/documen...to-number.html

[AlKey]

@AlKey: and more points for brevity. Now I'm getting embarrassed

I only read the original post. The OP didn't mention that there will be dates pre-1900

[TMS]

But kuTools is not free, if I understand correctly.

[MarvinP]

Trevor - You are correct (no surprise)

Then use this function to convert the text to month number.

=MONTH(DATEVALUE(A1&"1"))

This works for long MMMM and short MMM text.

Month Text to Month Number.xlsx

[AlKey]

This will do it

Please Login or Register  to view this content.