Date to text conversion for a column

**enthusiastic amateur** · 06-07-2017, 03:14 PM

Hi All, first request for help and it feels great to have somewhere else to turn apart from banging my head against a brick wall.

I have a workbook, with various worksheets, each hold different data which is validated on the sheet and when the user has fixed any issues they press a button that saves the file as a csv.. simple.. but no.

One of the worksheets has a column for date, which is displayed in the format "yyyy-mm-dd hh:mm:ss", but when looking at the actually contents of the cell in the edit bar it appears as "yyyy//mm/dd hh:mm:ss"

I've got the file creation working nicely, but when I open the file it appears as a number, so when opening it in excel it displays as the yyyy/mm/dd hh:mm:ss. I need the csv to hold the date as yyyy-mm-dd hh:mm:ss as this data gets loaded into an enterprise platform and not an option to change it.

I need the data change for a range of cells in E5:E5000

I've unsuccessfully tried the TEXT(),I'm sure there's a way to do it as seem similar examples, but not one for this situation

Any ideas gratefully received.
Thanks

PS- should this be in the Commercial section, if so what level of points, feels like a relatively simple one, but as I don't know the solution no idea)

**leelnich** · 06-07-2017, 03:23 PM

Hi EA, welcome to Excel Help Forums! This should convert a serial date in B4 to properly formatted text:
=TEXT(B4,"yyyy-mm-dd hh:mm:ss")

You should be able to copy the cell with the formula and paste its VALUE in the csv.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

**enthusiastic amateur** · 06-07-2017, 03:25 PM

Wow, that was quick. Thank you so much
How, do I select the columns it needs to apply to... I am very new at this and not sure how to create the sub for this successfully

**leelnich** · 06-07-2017, 03:29 PM

Wait, sorry, you want to do this in vba. Back in a few...
Ok, try this:

Please Login or Register  to view this content.

NOPE, that doesn't work...

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

**leelnich** · 06-07-2017, 04:35 PM

WOW, Excel REALLY hates Dates as text. Only way I could do it is to put an apostrophe in front:

Please Login or Register  to view this content.

I ran it on some test data, saved as .csv, then opened the csv in Notepad. Appears to save correctly (no apostophe).

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

**enthusiastic amateur** · 06-07-2017, 04:47 PM

THanks Lee,
I've just added it in and tried to run it and got an error message.

.errormessage.PNG

This is the first action as part of the file save routine I have,
as I want to convert the data first, then asks for the range to be saved and then it saves it as a csv

The full code (cobbled together from looking all over the place is)...but the rest of it works but I do want to remove the define the range bit to get it to work it out itself, but thats a battle for another day

PS You'll get a view on how out of my depth I really am I suspect by the code below, but learning a lot with each problem

Sub Save_CSV_5_Offer_EnterRange()

Range("E5:E5000").NumberFormat = "yyyy-mm-dd hh:mm:ss"

For rw = 5 To 5000
Range("E" & rw).Value = .Text
Next

Dim MyPath As String
Dim MyFileName As String
Dim WB1 As Workbook, WB2 As Workbook

Set WB1 = ActiveWorkbook
MyFileName = "Offer-" & Format(Date, "yyyymmdd-") & Format(Time, "hhmmss")
FullPath = WB1.Path & "\" & MyFileName

'(1) either used range in active sheet..
'ActiveWorkbook.ActiveSheet.UsedRange.Copy

'(2) or alternatively, user selected input range:
Dim rng As Range
Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="A5:J", Type:=8)
Application.ScreenUpdating = False
rng.Copy

Set WB2 = Application.Workbooks.Add(1)
WB2.Sheets(1).Range("A1").PasteSpecial xlPasteValues

Application.DisplayAlerts = False
'save file
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
With WB2
.SaveAs Filename:=FullPath, FileFormat:=xlCSV, CreateBackup:=False
.Close False
End With
Application.DisplayAlerts = False
End Sub

**leelnich** · 06-07-2017, 05:38 PM

I changed post# 5 code. Please give that a try. Thanks - Lee

ps. Range is on ActiveSheet by default. If you want cells from a different sheet, you must qualify the reference.

**enthusiastic amateur** · 06-07-2017, 06:01 PM

Yeah!!! Thanks. I confused myself to begin with by looking at the generated files in excel.. dooh.. when looking in Notepad...there were those lovely formatted columns... now I have de-construct the code so I can work learn from it... thank you

**enthusiastic amateur** · 06-07-2017, 06:38 PM

Hi Lee, This works and creates the output, would you be surprised that it takes a over 10 minutes to run for the 5000 rows?
Thanks
Graham

**xladept** · 06-07-2017, 07:52 PM

See if this works and is quicker:

Please Login or Register  to view this content.

On second thought - Nevermind

**leelnich** · 06-07-2017, 08:32 PM

Very surprised. Try the following in future. Same code, but I turned off ScreenUpdating and Calculation- the usual suspects for slow processing:

Please Login or Register  to view this content.

ps. How long was it taking before you added the conversion code?
pps. BTW, your last line of code should be Application.DisplayAlerts = True.

**enthusiastic amateur** · 06-08-2017, 05:15 AM

Hi Lee, That's done the trick, much quicker.

Before the new version of code, with 5000 rows to check, I actually gave up waiting as it was over 10 mins and instead reduced the row quantity down to speed it up.

The new version takes seconds to do the 5000.. and I also added another column into the sub (the end date) as a second For/Next group.. worked like a dream and I didn't notice any discernible speed difference...happy days!

Thank you so much for your help with it

Going to mark this as solved (once I work out how).

I also changed the setting as per you PPS, I'd turned this off to stop any odd messages appearing to the users, but setting it to True hasn't made a difference.

Now to look through the code to learn how it works for "next time"... the head scratching bit

**leelnich** · 06-08-2017, 07:13 AM

Excellent! Glad I could help, thank you for the rep. - Lee

**xladept** · 06-09-2017, 07:50 PM

I guess I'm confused - I assumed that you did this first and it didn't work - it works on my machine:

Please Login or Register  to view this content.

**leelnich** · 06-10-2017, 12:50 AM

When you say "it works..." what do you mean? The challenge was not simply to display the format, but rather to convert the number to text, and return it to the sheet without Excel automatically coercing it back to a number. I originally thought I could find a method that would work on the entire range, but after several false starts conceded to reading the range into an array, looping thru that to change the NUMBERS to formatted text with apostophe prefixed, then reading the array back to the sheet. I could also have changed the CELL formatting to text (@) to skip the apostophe, but the loop would still be required to change the NUMBERS. That's the best I could do, but I'm always open to a better way!

**xladept** · 06-10-2017, 08:33 AM

Hi leelnich,

I feel foolish now that you re-explain. My bad - nevermind

Date to text conversion for a column

LinkBack

Thread Tools

Rate This Thread

Display

Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Re: Date to text conversion for a column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Date Conversion-Text to DD/MM/YYYY

[SOLVED] Text Date conversion

[SOLVED] Preventing Date Conversion from Text

Year Date Conversion To Text

conversion of text to time and date

Date to Text Conversion

automatic conversion of text into date :(

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions