cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbungay
Frequent Visitor

Excel date format - saving date into excel file from a date picker

Hi

I don't understand what going on here, but I still relatively new to power apps. 

I am trying to save a date from date picker into excel file. My date picker looks like but its inserted into excel like December 22,2020.  

jbungay_0-1608656917524.png 

 
 
 
 
 
 
 

This is really confusing me. I have the excel date column format matches the power apps. Capture.PNG

The code to patch the date to excel is  simply  

 

 

 

patch(dateexcel, default(dateexcel), {StartDate: dtpStartDate.SelectedDate})

 

 


I don't get it. I sure its something simple but I have tried everything I can think off. 

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

Hi@jbungay,

Based on the issue that you mentioned, do you want to patch the DatePicker selected date to the Excel?

Could you please share a bit more about that if it is necessary for you to create a Date column in the Excel table?

If it is not, I think a general column type would be much more stable.

I have a test on my side, two columns for example, a General and a Date, please check as follows.

Set the OnSelect property as below:

 

Patch(
    Table1,
    Defaults(Table1),
    {
        StartDateGeneral: Text(
            DatePicker1.SelectedDate,
            "[$-en-US]mm/dd/yyyy"
        ),
        StartDateDate: DatePicker1.SelectedDate
    }
)

 

And I insert a Gallery to test what the date format will be update into the Excel, please check as follows:58.png

In a word, if you create a General column to store the date, it will be stable and be updated as the format you want. If you create a Date column, you could change the format using the Text() function.

On you side, please modify your formula as below.

 

Patch(
    dateexcel,
    Defaults(dateexcel),
    {
        StartDate: Text(
            dtpStartDate.SelectedDate,
            "[$-en-US]mm/dd/yyyy"
        )
    }
)

 

Hope it could help.

Best Regards,

Qi

 

 

View solution in original post

7 REPLIES 7
Drrickryp
Super User II
Super User II

Hi @jbungay 

For all matters of date and time, I rely on @CarlosFigueira.  Perhaps he will take a look at your issue.

v-qiaqi-msft
Community Support
Community Support

Hi@jbungay,

Based on the issue that you mentioned, do you want to patch the DatePicker selected date to the Excel?

Could you please share a bit more about that if it is necessary for you to create a Date column in the Excel table?

If it is not, I think a general column type would be much more stable.

I have a test on my side, two columns for example, a General and a Date, please check as follows.

Set the OnSelect property as below:

 

Patch(
    Table1,
    Defaults(Table1),
    {
        StartDateGeneral: Text(
            DatePicker1.SelectedDate,
            "[$-en-US]mm/dd/yyyy"
        ),
        StartDateDate: DatePicker1.SelectedDate
    }
)

 

And I insert a Gallery to test what the date format will be update into the Excel, please check as follows:58.png

In a word, if you create a General column to store the date, it will be stable and be updated as the format you want. If you create a Date column, you could change the format using the Text() function.

On you side, please modify your formula as below.

 

Patch(
    dateexcel,
    Defaults(dateexcel),
    {
        StartDate: Text(
            dtpStartDate.SelectedDate,
            "[$-en-US]mm/dd/yyyy"
        )
    }
)

 

Hope it could help.

Best Regards,

Qi

 

 

View solution in original post

CarlosFigueira
Power Apps
Power Apps

Unlike many other data sources that are supported by Power Apps, Excel columns don't have a predetermined type - you can store anything in column in an Excel spreadsheet, including mixing numbers and text (and formulas, and true/false values, ...). Power Apps, on the other hand, is a "strongly-typed" platform, meaning that it requires, among other things, that columns have a predetermined type. So when you add a connection to an Excel spreadsheet, Power Apps will try to guess the type of the column from its values.

Based on the image I see in the question, the first few rows in your table have an empty value for the StartDate column. I'm guessing (I don't know exactly how the logic works) that Power Apps will use the type that is more flexible: text. And when you are sending a date in the Patch statement to Excel, it is using the default Date-to-Text conversion (which is what you would get if you had a label with the expression Text(dtpStartDate.SelectedDate)).

To fix this you have a couple of options. You can save the dates as text in the excel file, and use the Text function as suggested by @v-qiaqi-msft to format it the way you want. Or you can make sure that you have at least a few lines in the beginning of your Excel table before adding it as a data source to Power Apps, and that will make it "guess" the correct type in your Excel file.

Thanks for the reply.  

Date_Z: Text(dtpStartDate.SelectedDate,"[$-en]mm/dd/yyyy")}),
Date_A: Text(dtpStartDate.SelectedDate,"[$-en]mm/dd/yyyy")}),

Both Date_Z and Date_B are date columns in the my excel file, but one gives an syntax error and one the other does not. The error states basically states, the types don't match expected Datetime and got Text.

If understand you then, Power apps ignores the data type definitions in Excel and tries to determine the data value based on first few rows on data. So there really in no reason to define types in the excel file.


@jbungay wrote:

Both Date_Z and Date_B are date columns in the my excel file, but one gives an syntax error and one the other does not. The error states basically states, the types don't match expected Datetime and got Text.


Probably one of those columns was imported as text, and the other as date/time. If you want to use the text value, you can make sure to add the dates in the first few columns in the Excel file as text (i.e., using the ' prefix when entering a date):

ForumPost001.png 


@jbungay wrote:

If understand you then, Power apps ignores the data type definitions in Excel and tries to determine the data value based on first few rows on data. So there really in no reason to define types in the excel file.


You can define the types (format) for the columns that have valid values. I'm not sure about how the logic works to determine the types of the rows, but what I've observed is that formatting empty cells doesn't seem to affect this determination.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (64,272)