Dates Difference in PowerApps and Excel

[MH3]

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

POWER APPS

[MH3]

Sir @WarrenBelz 

I was using your old Formula of Update: 

DateValue1.SelectedDate + Time(Value(Left(HourValue1.Selected.Value,2)+5) , Value(Right(HourValue1.Selected.Value,2)), 0)

The time is showing Correctly in PowerApps as expected, but in Excel it is broken
I have set the column format to Time (3/14/12 1:30 PM)

Any help in this?

[WarrenBelz]

Hi @MH3 ,

Yes that is correct - 12:00PM is noon, 12:00AM is midnight.

There is much debate on this but PowerApps and I suspect most data sources are coded this way.

I think the logic is changing the AM/PM on an o'clock as 12.01PM is one minute past noon and 12:01AM is one minute past midnight. I also need to reiterate that this is a complete work-around of the "standard" setup on the system.

Your other question - my formula will only work if you do not go past 6:59pm or before 7:00am

I am simply "grabbing" the hour entered, turning it into a number and if less then 7 (you had no AM choices before this), then it must be PM they have chosen, so (for instance) if 2PM chosen you need to send back 14 (+12) as the data source is in 24 hour format.

[MH3]

Oh Yes, @WarrenBelz  I understood!

and the old formula of yours is working fine, I was reading it wrong.

can I change this drop down into 12 hour format?

 
I mean can I change 13:00 to 01:00 will it count it as 01:00 PM ? and so on till 17:00 as 05:00? to make it easy for the user.

[WarrenBelz]

Hi @MH3 ,

I have been testing all of this on a SharePoint test list and it has all worked - clearly Excel does not like modified formulas.

I also have significant issue with dates in the few Excel reference lists I have left and avoid them (Excel) if I can.

If you put the old formula back, I am assuming it works, therefore you may have to retreat to DateAdd on the whole data we started with and forget about all the enhancements - or move it all to SharePoint (and it will all work without any adjustments).

[WarrenBelz]

@MH3 ,

I think we are one message behind each other here and yes - that is what the +12 does providing you only go from 7:00 am to 6:00pm.

[MH3]

Yes, Sir @WarrenBelz  you are absolutely correct.

Sometimes it is showing correct time, and sometimes it shows that weird date and time why it's doing like that?

[WarrenBelz]

@MH3 ,

I am out of ideas sorry. If Excel is given a standard format date in a standard format control it seems to work fine. We have modified this considerably (and as I said from the start, everything I have suggested is not standard code). I would seriously consider SharePoint as a data source.

[MH3]

Yes Sir @WarrenBelz  I understand Excel has some limitations!

Thanks for all your help!

and I want to change the drop down values from 13:00 to 01:00, 14:00 to 02:00  till 17:00 which is 05:00 , will your old formula still work? 

[MH3]

Sir @WarrenBelz  

I have changed the Items of Time Selector from 

and changed it to 13:00 to 01:00, 14:00 to 02:00

 
Now, in PowerApps it's showing 01:00 AM for 01:00 and 02:00 AM for 02:00, I wanted this to be in PM to make it as a 12 hour time.

any fixes?

[WarrenBelz · Accepted solution]

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