Date shown as number string instead of date

[ryanmcc]

I have a form that has 3 different dates on it. One seems to work great but the other two aren't seen as dates in the form editor. I am able to copy the date picker from the working one to enter the dates and everything shows on the excel spreadsheet correctly when a new form is created or edited. BUT, when you view the info, only the first date shows as a date and othe two show as a number string. I can't get a datevalue() function to work or text(#,"dd/mm/yyyy"). Any ideas what will help? How do I changed the "Data type" that is in the edit fields window?Edit Fields on left, how it shows up on right

[poweractivate]

@ryanmcc 

Try this:

1. Select the Form Control from the Tree view on the left 

2. Click Edit Fields

3. Click Add field

Then

4. Select the appropriate field you want that is of type Date / Time on your data source.

5. Click Add

6. You should now see the new Data Card, and it should be of type Date / Time

See if it helps @ryanmcc 

[ryanmcc]

@poweractivate  when I do that, the other two date fields are not in the Date data type.

[poweractivate]

@ryanmcc 

If the data type on the data source is not Date/Time you cannot store a Date/Time value in there.

Either create a new column of type Date/Time on the data source, or, you just cannot store a Date/Time and have to convert it to a Text representation of your choice.

If you want, try this and see if it works:

//pseudocode - change YourDateTime below to something appropriate for you
Text( YourDateTime, DateTimeFormat.LongDateTime )

more info:  Date/Time formatting with Text function

[ryanmcc]

@poweractivate  - Agree, the frustrating part is all 3 columns in the data course are Time. All three show up correctly in the spreadsheet as dd/mm/yy hh:mm. For some reason, when the App is displaying the date/time, it is showing as the number string "4481.5173611111". I forget how excel converts form that number to a date but I know that number is a date. 

I did try 

Text( YourDateTime, DateTimeFormat.LongDate )

but no luck. 

[poweractivate · Accepted solution]

@ryanmcc 

If you use Excel data source it stores it like this

Check if below formula converts that bizarre looking number to a Text version of the Date Time

//psuedocode - change YourDateField_Value to what's appropriate for you - this is where your number like 44840.09375 goes, for example

Text(
     DateAdd(
          DateTimeValue("1899-12-30 12:00:00 AM")
         ,RoundDown(
               (Value(YourDateField_Value) * 86400)
              ,0
          )
         ,TimeUnit.Seconds
     )
    ,ShortDateTime
)

Does it work @ryanmcc  ?

[ryanmcc]

@poweractivate That worked!!! Awesome job, much appreciated! 

[whuttn]

Hi @poweractivate 

I am having a similar issue. I tried that formula and I got a character unexpected error. Any advice?