Conversion date yyyymmdd en dd/mm/yyyy

Solved

Alain -
ucef - 25 Mar 2019 at 11:25

Hello,

In a column of dates written in yyyymmdd, I would like to convert the format to dd/mm/yyyy (Excel 2016)

Can someone help me

Thank you in advance

Alain

Configuration: Windows / Chrome 66.0.3359.181

4 answers

Answer 1 / 4

Best answer
The best answer is the one that has been validated by our teams.
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453

Hello
for a date in A1:
=DATE(LEFT(A1,4),RIGHT(LEFT(A1,6),2),RIGHT(A1,2))
Regards

--
The quality of the response mainly depends on the clarity of the question, thank you!
11
1. Alain
  
  Thank you Vaucluse, this formula works perfectly.
  However, if I complicate things, is it possible to get the result in the same cell?
  
  0
2. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453
  
  No, we can't place both a formula and a value at the same time; otherwise, we would have to go through VBA, and I'll let my friends (who participated here) handle that, it's not in my field.
  
  What is possible:
  process the field with the formula in a column outside the table
  copy the resulting field
  paste it onto the original cells using "special paste value"
  delete the formula field
  
  best regards
  
  0
Answer 2 / 4
gbinforme Posted messages 14930 Registration date Status Contributor Last intervention 4 744
Hello everyone,

So, as my friend Vaucluse has made a "provocation," here is the macro that modifies the number supposed to be a date 'yyyymmdd' into a real date 'dd/mm/yyyy' in the same cell of column A in the active sheet.
```
Public Sub formater() Dim cel As Range For Each cel In ActiveSheet.UsedRange.Columns(1).Cells If IsNumeric(cel.Value) And Len(cel.Value) = 8 And Left(cel.Value, 4) > 1900 _ And Mid(cel, 7) < 32 And Mid(cel, 7) > 0 And Mid(cel, 5, 2) < 13 And Mid(cel, 5, 2) > 0 Then cel.Value = CDate(DateValue(Mid(cel, 7) & "/" & Mid(cel, 5, 2) & "/" & Left(cel, 4))) cel.NumberFormat = "dd/mm/yyyy" End If Next cel End Sub 
```
Happy usage
--
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
2
Answer 3 / 4
jc80 Posted messages 5063 Registration date Status Member Last intervention 1 443

Hello

Convert a date from French format to English format

=TEXT(E2,"mm/dd/yyyy")

Best regards
0
1. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453
  
  Hi JC
  ??? I can't make your suggestion work in the requested case, that is, going from English to French
  it works well, for example, from 12/04/2018 to 20180412 but with:
  =E2;"aaaammjj") and still, without digitizing the result, which doesn’t seem possible
  
  but in the other direction, your formula returns #VALUE for me
  Best regards
  
  0
2. gbinforme Posted messages 14930 Registration date Status Contributor Last intervention 4 744
  
  Hello Vaucluse
  
  I can't get your suggestion to work
  and yet you handle dates well!
  Of course it is necessary that 20180412 was entered as a date not as a number, that is to say the value 43202 (12/4/18) and not the number 20180412 which exceeds Excel's date capacity ;-)
  
  Have a good evening.
  
  0
Answer 4 / 4
jc80 Posted messages 5063 Registration date Status Member Last intervention 1 443

Good evening Vaucluse and Alain
An example, but indeed I need to add a column.
The E2 being the original cell that I convert into F2
https://www.cjoint.com/c/HEEqSZVztyZ
Sorry for Alain
Best regards
0
1. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453
  
  Ok, it works, but GB, if the date is indeed entered as an Excel value in the format yyyymmdd, why complicate things with a reference and a formula, when you can simply change the cell format to DD/MM/YYYY
  for example, for today
  it would be 43250 in Excel which gives 30/05/2018 in dd/mm/yyyy and 20180530 in the yyyymmdd format
  no, or am I mistaken?
  Best regards and have a good evening
  
  0
2. gbinforme Posted messages 14930 Registration date Status Contributor Last intervention 4 744
  
  But of course, you are absolutely right, and to "convert the format" as requested, you just need to change it but the cell must be a date as was mentioned: “Elementary, my dear Watson”
  
  @jc80: you don’t need your column and formula because you just need to change the format since your dates are correct.
  
  @Alain, all you have to do now is tell us if your dates are dates or numbers.
  
  0
3. Alain
  
  Hello everyone,
  The dates are recorded as numbers 20180531, there is no date in this format. The number is therefore not recognized and cannot be converted into another date format.
  But the first answer from Vaucluse works well for me, I just wish to get the result in the same cell.
  A1: 20180531 plus formula gives A1 = 31/05/2018
  I can do it in two steps, putting the formula in B1 then replacing A1 with B1 (easier).
  In any case, thank you all for your help
  I'll come back if it doesn't work!
  
  0

Conversion date yyyymmdd en dd/mm/yyyy

4 answers

Pdf modification: online services and free programs

Change the toolbar font size

Canon pixma printer not detected via livebox 7

Booklet print mode on canon mg2550s printer

Problem with open office spreadsheet

How to pair a receiver with an external speaker without fi

Invisible text boxes or word forms on pdf

Reserve your whatsapp username for more privacy

Disable audio dubbing

I can't even find a single string.