Date column error, Error 1899

Help Studio
, ,
1

Hi team,

I see default 12/09/1899 in my date column.
Here are the expressions I used.
1)======threw the 1899 error
datetime.FromOADate(CDBL(in_qi_TransactionItem(“ActiveDate”).ToString)).ToString(“MM/dd/yyyy”)

  1. ======threw an error saying string was not recognized as a valid date Time
    DateTime.ParseExact(in_qi_TransactionItem(“ActiveDate”).ToString, “MM/dd/yyyy”, CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
    ======
    Can anyone alternative expression? Thanks!
2

DateTime.ParseExact(in_qi_TransactionItem(“ActiveDate”).ToString, “MM/dd/yyyy”, CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
I think can you try once “dd/MM/yyyy” in place of “MM/dd/yyyy”

3

Hi,

Can you check and share content of in_qi_TransactionItem(“ActiveDate”).ToString ?

Regards,

4

Hi @Yoichi
It does not seem working

Here is the expression

If(isnothing (in_qi_TransactionItem(“Hard”)) AndAlso String.IsNullOrEmpty(in_qi_TransactionItem(“Hard”).ToString),“”, DateTime.ParseExact(in_qi_TransactionItem(“ActiveDate”).ToString, “MM/dd/yyyy”, CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”))

5

Hi,

i mean, can you share content of in_qi_TransactionItem(“ActiveDate”).ToString using LogMessage etc as the following?

image

Regards,

6

@Yoichi

I am so sorry I do not quite understand.

7

@Yoichi

I want the following values to be populated to another file as is.
image

With my expression it populates 12/09/1899 default date

Thanks!

8

HI,

Can you put LogMessage activity which has in_qi_TransactionItem("ActiveDate").ToString just before activity which occur the exception?
Then check the value in Output panel.

Regards

9

@Yoichi

Sorry for the belated response. It results in the following number
-21
Please see the screenshot

image
image1767×432 90.8 KB

10

Hi,

Thank you for sharing.
-21 means 12/9/1899 in Excel Serial value. So where did you get this -21 from?
Is there possibility to get it from wrong column etc?

Regards,

11

@Yoichi
It’s coming from the correct column. And it is a formula column. the date values to be carried to the next template with the date format. Please let me know if you want me share the excel file with you. Thanks!

12

Hi,

If you use read it using ReadRangeWorkbook, can you try to use ReadRangeX (or classic ReadRange)?

image

Please let me know if you want me share the excel file with you.

If possible, can you share it?

Regards,

13

@Yoichi

The file was already read using the classic read range. My Bot account does not have O365 license as a result of that I do not use Excel Process Scope. If possible, I want to resolve using the expressions. Please let me know if I misunderstood you. Thanks a lot!

14

@Yoichi

Here is additional information, if it might help.

Let me try to explain a little bit
Column E is populated when there is data in Column C. We then populate a template column (on another separate file) data from column E after making sure that the data in Column E is converted to MM/dd/yyyy format. Can you please look into the attached excel file.
Here is also the expression I was trying to use that is resulting in 1899 error

if(isnothing (in_qi_TransactionItem(“Hard Date”)) andalso string.IsNullOrEmpty(in_qi_TransactionItem(“Hard Date”).ToString),“”, datetime.FromOADate(CDBL(in_qi_TransactionItem(“ActiveDate”).ToString)).ToString(“MM/dd/yyyy”))

Thanks a lot!
Test_Forum.xlsx (29.9 KB)

15

I think the above andalso should be OrElse. Can you modify it then try to run?

Regards,

16

@Yoichi

this time the date column is not populated (blank space).

17

Hi,

Can you check content of in_qi_TransactionItem(“Hard Date”).ToString ? I guess it’s empty.

Regards,

18

@Yoichi

I see -21

image
image1863×598 126 KB

19

It seems different column. In the above expression, the column’s name is “Hard Date”

20

@Yoichi

Understood. I renamed sensitive columns with other names.