[Webinar] Learn how to a build a cloud-first strategyRegister Now


conversion of date fomat

Posted on 2012-08-29
Medium Priority
Last Modified: 2012-08-29
I have a date  shown in excel column as Jul 27,2012 5:18 PM
need to conver it in 27-07-2012 have tried  text / Datevalue / value already but it gives me #value error . Also tried to change date format but it neither change it appearance nor it take any other format while  need to calculate days difference with now function with the above said date.
 Is there something to do with internal excel date format
Question by:Rajnish_Sharma_ibm
  • 4
  • 3
  • 2
  • +3

Expert Comment

ID: 38344818
Select the Cell
Click the Format menu  --> Cells...
Select Number Tab
Click Custom on the right hand column
Enter dd-mm-yyyy in the TYPE: box (middle of window)

That should do it for you !

Author Comment

ID: 38344825
No it's not working have tried this earlier too

Expert Comment

ID: 38344836
Have you actually typed the "Jul 27,2012 5:18 PM" - format will only work if it is some way a system generated number / date ?

It won't convert if you just type a date or if you imported the date from another system in plain text!

You could write a VBA Macro if all the dates were the same format - use ActiveCell.Value to grab the cell value and break up Jul and 27 with a token , which in this case would be a space, then use the comma to get 2012 and the same with the time using spaces and :

These could be stored in a set of variables in VBA or in an array and reformatted into the dd-mm-yyyy format and then just allocate the ActiveCell.Value to the variable storing the new format.

It would be quite a nice easy VBA routine to write. (Save the module and you can re-use it in other excel sheets)
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 38344880
This is a system genrated data and not a typed in i.e (copied form a csv file as pastespecial values option using a vba macro code ) extracted from a csv file. Is there is some system setting that could change eexcel default date format

Author Comment

ID: 38344891
Is there something to with Excel 2007 and windows 7 compatiability

Expert Comment

by:Elton Pascua
ID: 38344937
Any chance for a sample workbook?

Author Comment

ID: 38344944
sample workbook attached
LVL 34

Expert Comment

by:Rob Henson
ID: 38345047
The dates in your sample are all text so will not be recognised by standard numerical functions.

I have been able to strip off the date part of the text string:


However, I am working in excel 2003 so have not been able to convert to the result to a date; maybe later versions have better recognition of date formats.

Rob H
LVL 12

Expert Comment

ID: 38345070

If you double click the cell after setting custom format as dd-mm-yyyy (as shanemcd's comment ID: 38344818), the required format will be applied.

If you want to avoid double click of each cell, select an unused cell, format it to General, copy it, select all the dates cells and paste special>add>ok. It will do the trick.

Expert Comment

by:Elton Pascua
ID: 38345111
Highlight column B. Go to Data > Text to Columns. Click Finish.

Format the cells as dd-mm-yyyy.
LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 38345462
Hello techfanatic/rajnish

Text to columns should work if the date is in a recognisable format for your region, so it works for me if I switch to US regional settings, but doesn't work in my usual UK regional settings - if it doesn't work for you then perhaps temporarily change to US regional settings (through Control Panel)

....or another way is to use a formula, I think this will work for any region, in E2 copied down

=SUBSTITUTE(MID(B2,5,99),", ","-"&LEFT(B2,3)&"-")+0

format as required, see attached

regards, barry

Expert Comment

by:Elton Pascua
ID: 38347153
Thanks Barry - good information!

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question