foreign dates

[maureenmooney]

i have an excel table with dates in french (i.e. 27-févr-01),i tried changing it to english by changing the format and it didn't work. is there a way to do this? thanks.

 

[SQLBI]

Maureen,

It maybe that your version of Excel is treating the French date as Text and not as a date. I copied your example into a workbook and that's what happened to me.

You need to convert the Text to a Value, however if your version of Excel doesn't recognise Fevr as month 2 then i'm not sure how.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[DaleWatson123321]

Maureen,

Here's a solution for you. It involves using 8 formulas to separate the month/day/year of the French date, and to convert it to a normal Excel Date.

I can email you the file I have if you prefer, but here are the requirements...

1) You'll require a lookup-table for the French months. I'm not aware of the exact abbreviations used in the French version of Excel, but you should be able to get these abbreviations from viewing and copying your French dates (and removing the day and year). This way, you'll be sure to have the required special characters - e.g. the é in févr.

2) For your lookup table, place the month abbreviations in the left column and the corresponding month numbers in the right column (1-12). Place this table out of the way, off to the side.

3) Assign a range name to the table. In a formula below, I've used the name "mths".

4) Based on your dates being in Column A, enter the following formulas...

in B1: =FIND("-",A1,1)+1
in C1: =FIND("-",A1,FIND("-",A1,1)+1)
in D1: =MID(A1,B1,C1-B1)
in E1: =VLOOKUP(D1,mths,2,FALSE)
in F1: =VALUE(LEFT(A1,2))
in G1: =VALUE(RIGHT(A1,2))
in H1: =IF(OR(G1=0,G1=1,G1=2,G1=3),2000,G1)
in I1: =DATE(H1,E1,F1)

Note: The formula in H1 is required, otherwise the year will be 1900, 1901, etc. If you have dates beyond the current year, e.g. 2004, simply modify the formula and add the additional years - e.g.
=IF(OR(G1=0,G1=1,G1=2,G1=3,G1=4,G1=5,G1=6),2000,G1)

5) Next of course, you only need to copy the formulas down for as many dates as you have in Column A.

6) As the last step, you'll need to convert the formulas in Column I to values, before copying them to replace your French dates in Column A.

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Maureen,

A suggestion... In case you might not be aware, anyone can choose to mark a thread for e-mail notification. Therefore, in the event that you require a solution ASAP, you'll be notified via e-mail as soon as a posting is submitted.

Also, if you come across other interesting threads, you can keep track of them much easier once you've marked them for e-mail notification. For example, one of the choices for the listing order of threads, is by those check-marked - see the big red check-mark at the top center of the page - under "THREAD ORDER" - on the main Forum screen.

You can also go into your Personal Profile (see top-left-corner of screen) and set your profile so that you'll "automatically" be notified of postings for threads that you've either initiated or posted a response to.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca