Power Apps + Excel Online: RFC 3339 Date/DateTime Format Error When Patching to Excel Date Columns

Hi everyone,

I need help with a Power Apps canvas app connected to an Excel workbook stored in SharePoint/OneDrive. The app is a warehouse inventory system using Excel tables as the data source.

The app has these Excel tables:

tblSKU

tblLocation

tblInventory

tblTransactions

The main issue is with date fields when using Patch() from Power Apps to Excel.

Error Message

I keep encountering this error:

Expected value "" to be valid RFC 3339 'date-time' format. Allowed ISO 8601 format(s): 'YYYY-MM-DDThh:mm:ssZ', 'YYYY-MM-DDThh:mm:ss+hh:mm', 'YYYY-MM-DDThh:mm:ss'.

This happens during receiving / issuing / stock count transactions when patching to Excel.

Date Columns Involved

In tblTransactions, I have:

DateTime

TransDate

ProductionDate

ExpiryDate

In tblInventory, I have:

ProductionDate

ExpiryDate

LastUpdated

These columns are formatted in Excel as Date or Date/Time.

Current Patch Logic

I already changed my formulas to stop saving formatted text like this:

DateTime: Text(Now(), "dd mmm yyyy")

TransDate: Text(Today(), "dd mmm yyyy")

ProductionDate: Text(dpProductionDate.SelectedDate, "yyyy-mm-dd")

ExpiryDate: ""

Now I am trying to patch real date values instead:

DateTime: Now(),

TransDate: Today(),

ProductionDate: If( IsBlank(dpProductionDate.SelectedDate), Blank(), dpProductionDate.SelectedDate ),

ExpiryDate: If( IsBlank(varExpiryDate), Blank(), varExpiryDate ), LastUpdated: Now()

However, I still sometimes get the RFC 3339 error.

Sample Receiving Patch

Here is the relevant part of my receiving button:

Patch( tblTransactions, Defaults(tblTransactions), { TransID: Text(GUID()), DateTime: Now(), TransDate: Today(), Type: "RECEIVE", SKU: varSKU.SKU, Barcode: varBarcode, Description: varSKU.Description, Category: varSKU.Category, UOM: varSKU.UOM, LotNo: Upper(txtLot.Text), ProductionDate: If( IsBlank(dpProductionDate.SelectedDate), Blank(), dpProductionDate.SelectedDate ), ExpiryDate: If( IsBlank(varExpiryDate), Blank(), varExpiryDate ), Location: drpLocation.Selected.LocationCode, Qty: Text(Value(txtQty.Text)), User: User().FullName, Status: "POSTED", Remarks: "" } )

Expiry Date Calculation

The expiry date is calculated based on SKU shelf life:

Set( varShelfLifeDays, IfError( Value(varSKU.ShelfLifeDays), 0 ) ); Set( varExpiryDate, If( varShelfLifeDays > 0, DateAdd( dpProductionDate.SelectedDate, varShelfLifeDays, TimeUnit.Days ), Blank() ) );

Related Issue

In galleries and labels, Excel sometimes returns date values as serial numbers, for example:

46192

So I had to use formulas like:

Text( DateAdd( Date(1899,12,30), Value(Text(ThisItem.ExpiryDate)), TimeUnit.Days ), "dd mmm yy" )

This works for display, but I am still unsure about the correct way to patch date/date-time values back to Excel.

Any guidance would be very much appreciated.

Thank you.