Need to change UK Date Format

[PositionExcel]

I have a .CSV file in UK date format of DD/MM/YYYY. I need to delete the DD and just have the MM/YYYY format instead. Thank you!

[FDibbins]

This is probably already text (coming from a CSV file), but deleting the DD will for for sure make it text.

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Mon 14 Sep 2020) is actually 44088

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

[PositionExcel]

Thank you for your reply. I have thousands of lines with this date format and am hoping to avoid manual changes. Do you know if there's a way (perhaps a script) to achieve this?

[MrShorty]

As the say, the devil is in the details, and I am not sure I understand exactly what you want. You say that you want to "delete" the day information, but I wonder if you want to truly delete the day information, or if it is better to just "hide" the day information. To me, it usually seems preferable to keep the day information and just hide it rather than completely (and often permanently) deleting the information. With that in mind, here's how I would probably do it:

1) Import the text file normally. As Ford explained, Excel will convert the date/time information into a serial number that it uses to store and work with date/time information. Assuming that your computer's regional settings are different from the source file, you will probably need to pay careful attention to this step to make sure that Excel reads the DMY data correctly. If you are uncertain how to do this, this thread had some good discussion about importing text files with dates and helping Excel import the dates correctly: https://www.excelforum.com/excel-gen...ml#post5364448

2) With the dates imported correctly, then simple number formatting will easily display only the parts of the date that I want to see, while retaining all of the date/time information in the cell's value. Format as "mm/yyyy" to display only month/year for each date.

It all depends on exactly what you are wanting to do when you say you want to "delete" the day information from the source data. I think it would be preferable to hide rather than delete, but you may have other ideas. If you can help us understand better exactly what you want to do, we should be able to help you.

[PositionExcel]

I need to delete the day field for a software program that requires a mm/yyyy format to read the date. I don't think merely hiding it will satisfy the program. Thank you!

[MrShorty]

Still not sure I understand everything that is going on. Will this other program interact with Excel directly? If so, what is it looking to find in the Excel cells? Or are you needing to use Excel to create a new text file, and this program will interact with the text file?

[PositionExcel]

The program will be uploading the information from the excel (.csv) file. If the file date is not in a mm/yyyy format, the software program will not read it.

[MrShorty]

But will the program be accessing the data directly in Excel, or will Excel be writing the data to a .csv (text) file, and the program accesses the data in the .csv file?

Assuming the latter, then simply add a 3rd step to what I gave in post #4:
3) File -> Save as -> file type comma separated values text (.csv) -> give it an appropriate file name.

When Excel exports to a text/csv file, it writes the data as it is formatted in the cell. With a number format of "mm/yyyy", Excel should write that column to csv as formatted (you can open the exported file in a text editor to verify).

Is that what you need to do?

[PositionExcel]

Looping back to my original post, the .csv file was delivered in the DD/MM/YYYY format. In order for the software program I use to import the data, I need Excel to remove the DD and only have MM/YYYY.

[MrShorty]

If you follow the steps I outlined (keys: make sure Excel correctly imports the dates as real date serial numbers and make sure the number format on the date column is "mm/yyyy"), the new text/csv file should have text dates in MM/YYYY format. If you want to overwrite the old file rather than save as a new file, you can use File->Save instead of Save As or otherwise overwrite the old file.