Solved: Re: Dates Difference in PowerApps and Excel

MH3 · ‎04-17-2020

Hello,

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

POWER APPS

WarrenBelz · ‎04-19-2020

Thanks @MH3 ,'

You gave me a bit of a challenge here - note that I need you to understand this syntax to apply it.

So if you had a drop-down with the time values hard-coded like (note the leading zero)

["07:00","07:30","08:00"] and so on

if your controls were TimeValue1 (with the Items above) and DatePicker1, the Update of the card would be

DatePicker1.SelectedDate + 
Time(Value(Left(TimeValue1.Selected.Value,2)), 
Value(Right(TimeValue1.Selected.Value,2)), 0)

What I have done is taken the left two characters (hence the need for the leading zero) for the hour and the right two characters for the minutes.

The other things I should mention is that SQL syntax can be quite different to dealing with Excel and also SQL is a Premium connector, meaning that all users need a per user Power Apps licence (with the associated extra cost).

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.

View solution in original post

WarrenBelz · ‎04-20-2020

@MH3 ,

I have actually reconstructed the formulas with an Excel file in OneDrive.

I do not have the five hour difference, so cannot test this, however the AM/PM code seems to work MOST of the time.

Note that I cannot test any more than this - my code trial is a total workaround and I have no other ideas.

The top box is adding the five hours and works fine, but I have the same time zone, so Excel will simply reflect what I have input.

The formula has a slight change with some brackets -you will have to change the control names

DateValueName.SelectedDate + Time(
    (Value(HourValueName.Selected.Value) + 5),
    Value(MinuteValueName.Selected.Value),
    0
)

In the other one (doing the AM/PM conversion), I have replaced the With by doing the Variable on the submit of the form (With was getting some inconsistent results)

UpdateContext(
    {
        vHour: Value(
            Left(
                HourValueName.Selected.Value,
                2
            )
        )
    }
);
SubmitForm(YourFormName);
Refresh(YourTableName)

then your update

DateValueName.SelectedDate + Time(
    If(
        vHour < 7,
        vHour + 12,
        vHour
    ),
    Value(MinuteValueName.Selected.Value),
    0
)

the last thing is the Default of the second drop-down

Text(
    Hour(Parent.Default),
    "[$-en-US]00"
) & ":" & Text(
    Minute(Parent.Default),
    "[$-en-US]00"
)

This seems to work MOST of the time.

As I mentioned, this is a custom experiment and I think it is working as well as I can get it to do.

View solution in original post

WarrenBelz · ‎04-17-2020

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.

MH3 · ‎04-17-2020

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.

WarrenBelz · ‎04-17-2020

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.

MH3 · ‎04-17-2020

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

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

Pstork1 · ‎04-17-2020

@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.

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

MH3 · ‎04-18-2020

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!

WarrenBelz · ‎04-18-2020

@MH3

What about

DateValue1.SelectedDate + Time((Value(HourValue1.Selected.Value)+5), Value(MinuteValue1.Selected.Value), 0)

MH3 · ‎04-18-2020

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?

WarrenBelz · ‎04-18-2020

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.