Power Platform Community Forum Thread Details

Hello,

I am trying to develop an app that utilizes a barcode scanner to maintain when cylinders are checked in and out of “service.” i.e. when cylinders run out, users will scan the barcode on the cylinder to pull up the appropriate entry. These entries are stored on an Excel Online Spreadsheet (Linked via One Drive for Business) containing a Table, which pulls up the data for the app.

On my edit form:

Three dates and times (each in its own respective data card) are listed on each entry form (ViewForm and Edit):

Received on Date/Time: When cylinder is received on site

In Service Date/Time: When cylinder is placed in Service

Out of Service Date/Time: When cylinder is placed out of Service

There are Checkboxes (each in its own respective data card) attached to each datetime entry (on Edit form):

When the Checkbox is selected, the datetime entry populates with the Current Date and Time (or is supposed to)

Issues:

The Current populated Date and Time seems to have a mind of its own when it comes to what ends up being uploaded to the Excel Online table. At first, Excel was updating the time from the App (correctly displayed on the app in the View form after it was submitted) to UTC time, which in my case is +8.00 because I am on Pacific Time.

I utilized the DateAdd function along with the TimeZoneOffset() property, and this uploads the “current” local time (PST) to the Excel Online Table, which is exactly what I’m looking for. However, this only occurs some of the time. I can’t figure out why it converts the date submitted on the Edit form to the correct time in the spreadsheet sometimes and sometimes it doesn’t.

Time zone settings for reference:

DateTimeZone on all three Datepickers (for date) is set to DateTimeZone.Local

Under Language and Time Zone within my settings from Excel Online, my time zone is set as such:

There are various forms of time and date errors that occur when the current time gets uploaded to the sheet.

Most common one is when it uploads the Correct Date but uploads a time of 8:00 AM. See Screenshot of the sheet below:

Other times, it will give the correct Date and Minute value, but the Hour will be set to 8.

^ Correct DATE and MINUTE displayed, but hour was off.
Correct date and time: 1/26/2023, 2:38PM

Still other times, the Correct Hour and Minute will display, but the Date will be off by +/- 1 day.

^ Correct time displayed here, but actual date is 1/27/2023

The following lists the code on each datepicker as well as each data card that the datepickers are located within the EditForm. As noted, each of the three dates will have respective code pointing to the appropriate checkboxes, dates, hours, minutes, etc.

Update property for each respective DataCard: (All three cards have the same code with respective Date, Hour and Minute Date Pickers):

ReceivedDate.SelectedDate + Time(Value(ReceivedHour.Selected.Value), Value(ReceivedMinute.Selected.Value), 0)

Default Date (All three dates have the same code with respective checkboxes):

If(

ReceivedCheckbox.Value=true,

Text(DateAdd(Today(),-TimeZoneOffset(), Minutes), ShortDate),

Parent.Default

)

InputTextPlaceholder for Date:

If(IsBlank(Self.SelectedDate), Text(Blank(), Self.Format, Self.Language))

Default property for Hour Value (All three Hour defaults have the same code with respective checkboxes):

If(

ReceivedCheckbox.Value=true,

Hour(DateAdd( Now(), -TimeZoneOffset(),

Minutes )),

Text(Hour(Parent.Default), "00"))

Default property for Minute Value (All three Minute Defaults have the same code with respective checkboxes):

If(ReceivedCheckbox.Value=true, Minute(DateAdd(Now(), -TimeZoneOffset(), Minutes)),Text(Minute(Parent.Default),"00"))

There are other things I would like to add to this app in terms of functionality, but as of right now the app is useless until I can guarantee that the date and time is correct when uploaded to the spreadsheet.

Happy to share any additional information or code regarding this app that may provide more clues into the problem.

I am new to PowerApps so there very well could be a simple fix that I am not seeing. Any help is appreciated.

Categories:

Building Power Apps