Perserve format from Data table to Excel sheet

Learn Academy Feedback
1

Hi,

I am trying to insert data from a data table into excel, but the data format is being changed automatically.

The problem occurs when the data is exactly “24:00” then it is transformed to “1/1/1900 12:00:00 AM” in Excel.
When the data is anything else like “0:09” then the format is preserved as “12:09:00 AM”

The data table is set to type “string”, which apparently is not recognized in Excel where it is transformed to “Custom”

Can someone help me in solving this issue?
image

2

@Karsten_Bertelsen How do you want to store the date only in HH:mm Format or along with the Date?

3

Hi SupermanPunch,
I would prefer the have it as HH:mm

4

Hi @Karsten_Bertelsen,
Do you have control over excel file, if yes. Best option is to keep this format of the column as custom. In this scenario hh:mm:ss AM/PM.

If you do not have control over excel file, In that case you have to parse in exact format.

datetime.parse(“timeString”,“hh:mm”,system.globalization.cultureinfo.currentculture)

5

And to add one more thing, In vb.net it start with 00:00-23:59, there is no 24:00

6

Hi @Lakshay_Verma
I think that you are right. In order to overcome this issue, I have just transformed each variable from “24:00” to “23:59”

Seems to be the only solution

1 Like
7

Yes, This is how excel interpret values

8

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