Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Force Excel Dates to ISO Format yyyy-mm-dd

Posted on 2014-01-15
4
Medium Priority
?
1,619 Views
Last Modified: 2014-06-04
I am running excel 2013 for windows. I was wondering if there was a way to force excel to format dates in ISO format automatically every time a date is entered.

To clarify, I export a csv file from a PowerSchool, which is for schools. From these csvs, I need to upload them to another site for reporting purposes. This site requires all dates to be in ISO (yyyy-mm-dd) format.

I know I can go in and manually format these cells, but sometimes there are alot of columns with these reports. In addition, every time any cell in the report is formatted incorrectly, the upload fails and you have to reformat the dates again in the csv because the changes never stick.

ANy help is appreciated.
0
Comment
Question by:ibewlocal98
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39782407
There is no way to force Excel to use a particular date format other than by creating a template, formatting the appropriate cells in a particular way and then protecting them against update.

You don't provide sufficient information to advise further and I don't know the PowerSchool application but, assuming you can't change that application to format the dates for you I would imagine you have to proceed as follows:-

1. Receive the csv file from PowerSchool
2. Load into excel to do some manipulation (including the date change)
3. save as a csv for upload to the website

Assuming these steps are correct you can modify the dates on loading into excel if, instead of double clicking the file to open it, you first open a blank excel spreadsheet and then from the Data Tab choose to import from a text file. If you follow the dialog you will see an option to import as 'date y-m-d' which you can apply to particular columns. If you save an excl spreadsheet at this point the changes will stick. You can then do a 'save as' to put the file back into CSV for upload.

if the dates are not aligned in columns you would need to do a 'find and replace' using the default date 'format' for find and the new date format for 'replace' - it may take some trial and error to get the correct format unless the incoming data is 'clean'.

Once the formats have been changed then again save in excel format to make them 'stick' and save as csv for upload.

If this is a regular occurrence then I suggest it would be better to have a macro that reads the incoming file, does the changes and spits out a file ready for upload and saves an excel copy.

Reg
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39782505
Hi,

you could use this macro to change the dates in ISO Text

Sub Macro()
For Each c In ActiveSheet.UsedRange
    If IsDate(c) Then
        c.NumberFormat = "@"
        c.Value = Format(c, "yyyy-mm-dd")
    End If
Next
End Sub

Open in new window


if you open the csv in XL it will interpret them as dates and reformat them

Regards
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 39784220
Does this formula work for you...?
=YEAR(C9)&"-"&MONTH(C9)&"-"&DAY(C9)

however, for months and days less than 10, you may lose the 2 digit format that you need...
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39785103
Or:

=TEXT(C9,"yyyy-mm-dd")
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

722 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