Dates Difference in PowerApps and Excel

(0) Share

Report

Posted on by MH3

1,059

Hello,

@Pstork1

I have a Date Picker in my app, and the timezone is set to Local.

when I pick a Date it shows correctly in PowerApps Display Form and Excel sheet where it is storing, but the Problem is:

Time: when I pick the time in Power Apps is Hour Value = 04, and Minute Value = 00.

it shows 4:00 PM in PowerApps but Stores 11:00 AM in Excel.!!

How to Fix this?

Any Help

ScreenShots:
Excel

Excel)log.jpg

POWER APPS

Categories:

Building Power Apps

I have the same question (0)

All responses (32)

Answers (2)

Sort by

WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @MH3 ,
Please have a look at the TimeZoneOffset function and also this recent post of mine on the subject. I believe the answer may be there for you.

Update - did not see the tag - I am sure @Pstork1 can also help you.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
MH3 1,059 on at

Like
a
(0)

Report
Copy link

Link copied!

Dear Sir,

@WarrenBelz

I have seen your post, and I actually facing problems with the time only, the Date is showing correctly in both Excel and PowerApps, but the Time is showing a different value.

can you help me out in the formula? where to put it and in which property?
I have an HourValue1 and MinuteValue1 Dropdowns. I select time from that and it shows different time in excel as compared to powerapps display form.

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Thanks @MH3 ,
The time is the TimeZoneOffset issue.
When you submit to Excel, you need to do the conversion in the Update property of the card. The other thing you can do if you know exactly what the difference will be every time is use DateAdd with the Hours parameter
DateAdd( YourDatePickerName.SelectedDate 5, Hours )
will add 5 hours to what is being posted in Excel and put it back to the correct time.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No

MH3

1,059 on at

Like (0)

Report

Copy link

Link copied!

Dear Sir,

@WarrenBelz

I have created the Records from 00:00 i.e 12:00 AM Time till 23:00 i.e 11:00 PM

in Power Apps they are good, but in excel


	12:00 AM PowerApps	7:00 PM
	1:00 AM PowerApps	8:00 PM
	2:00 AM PowerApps	9:00 PM
	3:00 AM PowerApps	10:00 PM
	4:00 AM PowerApps	11:00 PM
	5:00 AM PowerApps	12:00 AM
	6:00 AM PowerApps	1:00 AM
	7:00 AM PowerApps	2:00 AM
	8:00 AM PowerApps	3:00 AM
	9:00 AM PowerApps	4:00 AM
	10:00 AM PowerApps	5:00 AM
	11:00 AM PowerApps	6:00 AM
	12:00 PM PowerApps	7:00 AM
	1:00 PM PowerApps	8:00 AM
	2:00 PM PowerApps	9:00 AM
	3:00 PM PowerApps	10:00 AM
	4:00 PM PowerApps	11:00 AM
	5:00 PM PowerApps	12:00 PM
	6:00 PM PowerApps	1:00 PM
	7:00 PM PowerApps	2:00 PM
	8:00 PM PowerApps	3:00 PM
	9:00 PM PowerApps	4:00 PM
	10:00 PM PowerApps	5:00 PM
	11:00 PM PowerApps	6:00 PM

I have used your formula, but it doesnt worked.

Excel Column Data Type Annotation 2020-04-17 155500.png

Can you please take my session if possible, I have 1 other thing to do as well with time.!

Was this reply helpful? Yes No

Pstork1 69,657 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

@WarrenBelz glad you were able to jump in on this one. Been out for a few days trying to meet a project deadline.

@MH3 Let me provide a bit of background on what is happening. Power Apps is set to display the date/time in your local timezone. But Excel Assumes the values are for Universal Time Code (UTC). So for example, if I put an entry in an Excel file for 8:00 AM it will show as 3:00 AM when I read it and display it in a gallery because my local timezone is Eastern US which hours behind UTC. Excel assumes that the 8AM in the spreadsheet is UTC which would be 3:00 AM local time. So that is what it displays.

When you pick a date/time in Power Apps it will assume its local time. But when you save it to Excel it will assume its UTC. So when you read it back in its displaying the UTC date time as though it was local. You need to do the conversion manually either when you save or when you display in Power Apps.

Was this reply helpful? Yes No
MH3 1,059 on at

Like
a
(0)

Report
Copy link

Link copied!
Dear Sir @Pstork1
Thanks for your kind explanation about the backend work of the date time picker,

Actually there is a 5 Hour Difference in the values saved in PowerApps as compared with the Excel.
but I wanted to do the conversion while entering it into the excel, as Sir @WarrenBelz said, I have to use TimeZoneOfffset Function,
but I am confused where should I drop that formula in?
HourValue1: OnSelect Property on that Hour Value of Time Picker or in MinuteValue1 onSelect Property?

or by Selecting the Whole Date Time Picker DataCard and put the function in Update Property?

Right its using this Code in the Update Property:
DateValue1.SelectedDate + Time(Value(HourValue1.Selected.Value), Value(MinuteValue1.Selected.Value), 0)

Kindly Guide me!

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
@MH3
What about
DateValue1.SelectedDate + Time((Value(HourValue1.Selected.Value)+5), Value(MinuteValue1.Selected.Value), 0)

Was this reply helpful? Yes No
MH3 1,059 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes. @WarrenBelz it Worked!!

On Excel it Shows the Correct Time what I've Selected, but in PowerApps it Just Added +5 hours in Gallery to show the Time,

can I display that Time Same in both excel and in PowerApps Gallery?

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @MH3 ,
A conundrum here, where do you want to display it?
A gallery is easy - instead of ThisItem.YourFieldName, try
DateAdd( ThisItem.YourFieldName, -5, Hours )
If you want it in the input field where you change it, a bit more of a challenge.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
MH3 1,059 on at

Like
a
(1)

Report
Copy link

Link copied!

Yes, Sir @WarrenBelz It's Done!
Now, the gallery shows the time what I've selected and the excel also shows that time!

Thanks for your kind help and sorry I made you little disturbed but I was really trying to fix but I couldn't do it.

Was this reply helpful? Yes No