Unanswered

How can I patch time in a hh:mm AM/PM format??

(0) Share

Report

Posted on by TheodociusKMT

Hello, I'm new to Power Apps and I'm trying to create an application for updating the hours of operations for a business. The app is connected to an excel sheet on Onedrive. Things seemed to be coming along very well until I tried to actually push the patch through. I keep getting an error saying- "The value 'h:mm AM/PM' cannot be converted to a number"

The formula I'm using for the Patch is

UpdateIf(colData,true,{'Display Start Time':DateAdd(Text(DrpStartTimeHour.Selected.Value & ":" & DrpStartTimeMinute.Selected.Value & " " & DrpStartTimeAMPM.Selected.Value),"h:mm AM/PM"),'Display End Time':DateAdd(Text(DrpEndTimeHour.Selected.Value & ":" & DrpEndTimeMinute.Selected.Value & " " & DrpEndTimeAMPM.Selected.Value),"h:mm AM/PM"),'Start DOW':Text(drpStartDOW.Selected.Value),'End DOW':Text(drpEndDOW.Selected.Value),'Start Date':DateAdd(Text(drpStartDateSelect.Selected.Value),"mm/dd/yyyy"),'End Date':DateAdd(Text(drpEndDateSelect.Selected.Value),"mm/dd/yyyy"),'Message Text':Text(MessageInput.Text)}); Patch(Table1,colData)

The application looks like this so far and requires being able to select different times and minutes and pushing it through to whatever locations are checked off. Is anyone able to help me optimize this formula?

Attached is a pic of the error and the app layout

Categories:

Building Power Apps

pjROo.png

dM5vj.png

I have the same question (0)

All responses (5)

Answers (1)

timl 36,383 Super User 2025 Season 2 on at

Like (1)

Report
Hi @TheodociusKMT

In general, if you want to patch to a datetime column in a data source, you would patch a datetime object, and not a text value with an 'AM/PM' identifier.

With your formula, it looks like you're trying to call DateAdd to either format or to create a date object. This is not correct because DateAdd is used to carry out date arithmetic. DateValue or TimeValue is what you're looking for.

This should take you closer to what you need.

UpdateIf( colData, true, { 'Display Start Time': TimeValue( DrpStartTimeHour.Selected.Value & ":" & DrpStartTimeMinute.Selected.Value & " " & DrpStartTimeAMPM.Selected.Value ), 'Display End Time': TimeValue( DrpEndTimeHour.Selected.Value & ":" & DrpEndTimeMinute.Selected.Value & " " & DrpEndTimeAMPM.Selected.Value ), 'Start DOW': Text(drpStartDOW.Selected.Value), 'End DOW': Text(drpEndDOW.Selected.Value), 'Start Date': drpStartDateSelect.Selected.Value, 'End Date': drpEndDateSelect.Selected.Value, 'Message Text': Text(MessageInput.Text) } ); Patch(Table1,colData)

The alternative that I recommend is that you store the hours, minutes and AM/PM identifier in separate columns in your Excel spreadsheet. Power Apps doesn't support Excel date fields well. It may try to convert the times to UTC so you'd be better off storing the hours and minutes separately.

Was this reply helpful? Yes No
TheodociusKMT 18 on at

Like (0)

Report

Thank you so much for this! It's almost got me to the finish line. Is there a way to incorporate timezoneoffset into that formula you gave me?? It's how I've gotten the right time to show up in the gallery section connected to the excel. So I'm thinking that maybe it could be incorporated into the patch in order balance out. (currently when using the formula, it tacks on an additional 5 hours) @timl

TimeZoneOffset(Now()-60),TimeUnit.Minutes

Was this reply helpful? Yes No
Verified answer

timl 36,383 Super User 2025 Season 2 on at

Like (1)

Report
Hi @TheodociusKMT

If for example, your timezone is UTC -5, to subtract 5 hours, you would amend your formula using this syntax:

'Display Start Time': DateAdd(TimeValue( DrpStartTimeHour.Selected.Value & ":" & DrpStartTimeMinute.Selected.Value & " " & DrpStartTimeAMPM.Selected.Value ), TimeZoneOffset(), TimeUnit.Minutes ),

Was this reply helpful? Yes No
TheodociusKMT 18 on at

Like (0)

Report

Thank you so much @timl !!! Your suggestions worked like a charm and I was able to get the app back on track!!

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report

You're welcome @TheodociusKMT - glad to help.

Was this reply helpful? Yes No