Here is how the date is in the dataset: 48:04.3.
Using this DateTime.FromOADate(Cdbl(convertDate)).ToString(“MM/dd/yyyy HH:mm:ss”)
but am losing the preceding 0 in the time for example 12am being 00 and I am losing the seconds.
Anyone have a different take on this one? It is being read from an .xlsm file. Tried preserve format however there is so much data the timing is not acceptable.
Thoughts?
maybe mistaken, but this value looks more as a format of 48 of X and 4.3 of Y and not like a oadate.
Can you elaborate more on the origin source and the context?
it is a date - the file is sent from a 3rd party source dealing with crypto. It is a date - appears as I show it in the macro file. If I use “HH.mm.ss” instead of “HH:mm:ss” then I do not lose data. Using “:” seems to be triggering a custom format in excel that I don’t want. I need to keep the format as “General” - example:
you need to use tt also in date format I guess without it it is taking the time in 24 hours format
can you try this MM/dd/yyyy hh:mm:ss tt
cheers
hh - 12 hrs format, will fail e.g. on 13:00
HH - 24hrs format
tt is AM/PM designator
ff… usable for miliseconds
Agreed with you…but I was giving the target format to avoid 00 in hours…
I hope I did not understand the question wrong
cheers
Thanks Anil - tried this yesterday originally and this did not take - trying a VB script to reformat the entire column
Hi
It is not the System Globalization or the Parse - it is the colon in the string statement that is triggering a format in excel that is removing the :ss and the leading 0 of the time. If I use a . separator instead of a : it works fine - working on alternatives
Used this vb script
Function ChangeColumnFormat(sheetName As String, columnLetter As String)
ActiveWorkbook.Sheets(sheetName).Activate
Columns(columnLetter & “:” & columnLetter).Select
Selection.NumberFormat = “mm/dd/yyyy hh:mm:ss;@”
ActiveWorkbook.Save
End Function
with parameters in the call VB activity of sheet name and column in an array - works
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
