Log In
Register
Home
April 23, 2015
Offline
Hi there,
I am having issues getting the date format to apply to a column of data. Every day I run an extract from a company database, the output is in csv format. I open this directly in Excel and then cut and paste into another workbook, (I know I should be automating this process but lets leave that for now :-))
As you will see in the file the exported date in column S is in date/time format, I have added columns T U and V to generate a date value without the time element. This has worked well for months, so not sure if something in the raw data has changed but now the date generated in column V is displayed as the Excel date number and I cannot get it to switch to a date format. Can anyone help?
Alan
July 16, 2010
Offline
Hi Alan,
I'd say if this has worked well for months then the date in the file must have been text format. Now the date in the file is a proper date serial number so when you concatenate it with other data it loses the formatting. You need to wrap the date in the TEXT function like so:
=TEXT([@[Last when downloaded (UTC)]],"d/mm/yyyy")&" "&[@Day]&" "&[@Month]
Mynda
April 23, 2015
Offline
Hi Mynda,
Many thanks for the help as always! I tried the TEXT function wrap as you suggested but I am still getting the same display, (updated file attached), any idea what I am getting wrong here?
Alan
Trusted Members
Moderators
November 1, 2018
Offline
Compare your formula to Mynda's - they are not the same. 😉
April 23, 2015
Offline
Well there's another special moment for me 🙂 Many thanks Velouria and apologies Myanda! I tried writing the formula from scratch when I received Myanda's original response - that was my mistake - a simple cut and paste of Myanda's formula did the trick. Thanks again.
1 Guest(s)
