Unanswered

How to format as date when a number but text if not

(0) Share

Report

Posted on by freshtomatoes

Hi guys,

My app is reading from an excel spreadsheet and some of the data in a date column is listed as TBC or second half of 2023 while the majority is in date format.

I was using the formula:

Text(ThisItem.Bronze, "dd/mm/yyyy", "en-GB")

To get the date showing right but obviously this isn't working for the mixed format data.

I tried this, but it didnt work:

If(IsNumeric(ThisItem.Bronze),Text(ThisItem.Bronze, "dd/mm/yyyy", "en-GB"),Text(ThisItem.Bronze))

Any pointers?

Cheers

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

rswain 173 on at

Like (0)

Report
You can modify your formula to use the DateValue function to convert the date strings to date values before applying the Text function to format them as dates. Here's an example: If(IsNumeric(ThisItem.Bronze), Text(DateValue(Text(ThisItem.Bronze, "[$-en-GB]dd/mm/yyyy")), "dd/mm/yyyy"), ThisItem.Bronze) This formula first uses the Text function to convert the date string to the date format "dd/mm/yyyy" using the en-GB locale. It then uses the DateValue function to convert the resulting string to a date value. If ThisItem.Bronze is not numeric, the formula simply returns ThisItem.Bronze as text without trying to convert it to a date.

Was this reply helpful? Yes No
freshtomatoes 30 on at

Like (0)

Report

Thanks for your help @rswain, unfortunately that has given me an error on one location and then incorrect dates in another.

One set of dates looks like this:
despite the source dates looking like this:

The next table I used it in, does not show the dates but gives me this error:

Do you know what could be causing these issues?

Cheers

Was this reply helpful? Yes No
Cat Schneider 488 Most Valuable Professional on at

Like (0)

Report

Try using:

Text(ThisItem.Bronze, "dd/mm/yyyy;@")

Was this reply helpful? Yes No
freshtomatoes 30 on at

Like (0)

Report

Thanks @Cat ,
That doesn't seem to work either, I get the following error:

And it shows like this: (there should be TBCs in the Bronze columns that don't have dates)

Was this reply helpful? Yes No
Cat Schneider 488 Most Valuable Professional on at

Like (0)

Report

Hmm... perhaps then if the first thing you used was working for your dates, and not the others, try:

IfError(Text(ThisItem.Bronze, "dd/mm/yyyy", "en-GB"), ThisItem.Bronze)

Was this reply helpful? Yes No
freshtomatoes 30 on at

Like (0)

Report

Thanks @CatSchneider but that doesn't seem to work either. That gives me the same error and stops showing the non-numbers.

Was this reply helpful? Yes No