Excel date format issue

[iiintro]

 Hello 

I'm trying to save date from PowerApps to Excel and display the date in PowerApps.

First, I have set the column as Date Time format in Excel.

Sencond, the formula in PowerApps to save Date is as below:

If(StartsWith(Dropdown1.SelectedText.Value, "生产开始") && ProductForm.DisplayMode=DisplayMode.Edit,
Text(DateAdd(Now(), Value(First(Filter(BC_Time, SAP_TEXT = Gallery1.Selected.'SAP Code')).KeyTime), Minutes), "yyyy-mm-dd hh:mm"),Text(DateAdd(Gallery1.Selected.预计生产完成, -8 ,Hours),"yyyy-mm-dd hh:mm"))

Then, the date can be saved in Excel successfully as below:

Meanwhile I can view the date in PowerApps as below:

But when I refresh the Apps while I'm editing the other function, it shows error as below:

So I have to use DateTimeValue to switch the text as DateTime as below:

And now it saves the date in Excel as the second item in Excel:

And after I refresh the browse, PowerApps will say the Gallery1.Selected.预计生产完成 is DateTime. I have to delete the DateTimeValue again.

Why this happens? How to make it,Gallery1.Selected.预计生产完成,  always be DateTime or Text?

[iiintro]

After I add the DateTimeValue, and then refresh the page, it shows it's DateTime now..

[v-bofeng-msft]

Hi @iiintro :

Could you please tell me:

What is "预计生产完成" columns's data type?

How do you patch rows to the excel table?Could you please consider show me the formula?

If you use a form to create a new line,could you please show me the setting of the form and the update property of the "预计生产完成" data card?Could you please show me some screenshots?

Best Regards,

Bof

[iiintro]

In Excel, "预计生产完成" columns's data type is Time.

The Patch Formula is as below:

Patch(
Table2,
First(
Filter(
Table2, Pro_No=DataCardValue1.Text)),
{产生状态:Dropdown1.SelectedText.Value, 预计生产完成:DateAdd(DateTimeValue(DataCardValue8.Text),-TimeZoneOffset(DateTimeValue(DataCardValue8.Text)),Minutes), 预计充填时间:DateAdd(DateValue1_1.SelectedDate+Time(Value(HourValue1_1.Selected.Value),Value(MinuteValue1_1.Selected.Value),0),8,Hours)});

[v-bofeng-msft]

Hi  @iiintro :

The Point is that:

• You use date time type value to update the "预计生产完成" field.
• The Form submit the record and set the "预计生产完成" field in Text type.

So, it depends on whether you want to use the date type to update the "expected production completion" field or use the text type to update the "expected production completion" field.

If you want to save the value in date time mode

please try:

Set the data cards's Update property to:

DateTimeValue(DataCardValue8.Text)

Best Regards,

Bof

[iiintro]

Hello Bof

I tried to set it as DataTimeValue, but after I refresh browser. A lot of errors occur again.

And It shows 预计生产完成 as Text again

[v-bofeng-msft]

Hi @iiintro :
After submitting the data in this form, is the date still stored as a text?I've made a test but it worked well on my side:

Best Regards,

Bof

[iiintro]

Hi Bof

No matter if I update the Update content, nothing changes. And I guess it's because I Patch the Input box content directly while not the Card update information？

Besides, could you please help me delete the picture that content a lot of information in the card in below reply. I've deleted mine. Thanks very much.

[iiintro]

Hello Bof

Thanks your help. Could you please do me a favor to delete the second picture? 

Thanks again

[v-bofeng-msft]

Hi @iiintro :

Which picture do you want me to delete, could you please show me  a screenshot?

Best Regards,

Bof