Got a stumper on date time with From OA Date

Help Studio
, ,
1

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?

2

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?

3

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:
image

4

image

5

@Chris_Bolin

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

6

hh - 12 hrs format, will fail e.g. on 13:00
grafik

HH - 24hrs format
tt is AM/PM designator
ff… usable for miliseconds

7

@ppr

Agreed with you…but I was giving the target format to avoid 00 in hours…

image

I hope I did not understand the question wrong

cheers

8

Thanks Anil - tried this yesterday originally and this did not take - trying a VB script to reformat the entire column

1 Like
9

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

10

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

1 Like
11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.