Hey all, using the Advanced Excel module I am currently trying to create a column where the cell type is "datetime" I am passing in the date value from the database but when opening the excel file, the nulldate values are not the OS nulldate they are: "1900-01-02". The problem I am trying to solve is actually that I don't want to show any date at all when the value passed in is a nulldate?
Hi,
You can check the date at the time of mapping like:
If(ReportRecords.Current.SettledDate = NullDate(),"",ReportRecords.Current.SettledDate)
or
If(ReportRecords.Current.SettledDate = "1900-01-02","",ReportRecords.Current.SettledDate)
Hope that is what you asked for.
Regards,
Anees Mansoori
Thanks for the quick response but the problem is that if I put an empty string value into the CellValue, it errors and says the "" is not a valid DateTime
Not sure what will you get in excel but can give a try to
If(ReportRecords.Current.SettledDate = NullDate(),TextToDateTime(""),ReportRecords.Current.SettledDate)
If(ReportRecords.Current.SettledDate = NullDate(),TextToDateTime(NullTextIdentifier()) ,ReportRecords.Current.SettledDate)
Ended up figuring this out, maybe it's not the most elegant solution
cellValue: If(date = nulldate(), "", date)
cellType: If(date = nulldate(), "general", "datetime")
Just double check in excel output also.
Yeah it works and even the cells that are supposed to be "general" are of type "date" which is what I wanted, I guess general is a sort of generic type.