I'm having trouble coming up with a solution for my Date data type fields. The end-user is wanting to see only month/day hour:min AMPM format within the form that he runs, but also wants to export this data to Excel. The problem is that he also wants conditional formatting on the date, so when something is 3 days or older, it will turn orange, and when 5 days or older, then it will turn red. The problem is that if I use the Format() function within my query, it turns my dates into a String, which doesn't allow me to use conditional formatting properly when using the "mm/dd hh:nn AMPM" formatting. However, if I switch it around and instead of the query formatting, I do the formatting within the form in the format property field, then the conditional formatting works properly, but when I try to export to Excel, I get the error message: "We found a problem with some content in 'Filename.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

I am stuck at this point because I am not sure how I could get both conditional formatting working and the export to Excel to work as well. If I use the Format() function within the query, then conditional formatting doesn't work but I can easily export to excel without any issues.

Thanks in advance,
Peter

---

Reply from Alex Hedley:

How are you exporting the data? Are you just using the ribbon?

You should be able to format the Textbox on the form so the underlying data isn't changed.
The conditional formatting can be gone against the field.
Then just export the underlying query.
You could always write some automation to format the data in the excel sheet after it has been exported