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]

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.

[MH3]

Ok Sir @WarrenBelz , @Pstork1 

Actually I am developing an Activity Time log Application for my client.

I have an another Requirement from my client that they want a Single Time Selector Like:

9:00  AM

9:30 AM

10:00 AM

10:30 AM

1:00 PM (afternoon)

and till

5:30 PM

in a single Box , 

So, What I did is this  I typed in the time values in the HourValue1: Items Property

and in the Card, update Property I did like this:

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

Because I added the minutes in the HourValue1, items property, in that way I changed the formula.

But when I selected 3:00 (for my local time its 3:00 AM) it gave me 12:00 AM both in excel and PowerApps.

How can I achieve this with a Single Time Selector with Custom values of Hour and Minutes ?

[WarrenBelz]

HI @MH3 ,

Firstly, I cannot reproduce this - see below. The label has your exact formula in it.

Can I make another suggestion here? Use SharePoint as the backend and all of this will go away.

Excel has so many other limitations including 2000 row limits and the sorts of issues you are facing here and really is not designed to be scaleable for a primary data source. I have only ever used it for static reference tables and even that not for quite some time on new apps.

If you are building an app for a client, I would certainly start with SharePoint.

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]

Yes, Sir @WarrenBelz  you are absolutely correct!

but the Client said, the original DataSource will be SQL Database, excel was just for testing that everything is working fine or not like editing, deleting and updating.

So, as my last question can you help me in that? is that possible to have a single time Selection with hours and minute value?

eg

and after 12:00 it Should change AM to PM (afternoon).

any help!

[WarrenBelz · Accepted solution]

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.

[MH3]

Yes, Sir @WarrenBelz It Worked as expected!!
Thanks for all of your help!

I have 1 question here, can I set the selection of time in a 12 hour format? to mate it easy for the users

like

9:00

9:30

...

12:30

..

1:00 PM (afternoon)

1:30 PM 

...

5:30 

as I have set the HourValue1: Items to these 

["9:00","9:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30","14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30"]

I want this into a 12 hour format,

["9:00","9:30","10:00","10:30","11:00","11:30","12:00","12:30","1:00","1:30","2:00","2:30","3:00","3:30","4:00","4:30","5:00","5:30"]

but when I select 1:00 which is 13:00 it shows 12:00 AM not 1:00 PM

How can I fix this?

[WarrenBelz]

Hi @MH3 ,

Firstly, you need 09:00, not 9:00 to make my workaround function (I am taking the first two characters) and I am sure the PowerApps designers never had this one in mind, however I have fresh Monday morning brain here in Oz and I think the below will work - it is valid syntax - you just need to test it.

With(
    {
        vHour: Value(
            Left(
                Dropdown29.Selected.Value,
                2
            )
        )
    },
    DateValue3.SelectedDate + Time(
        If(
            vHour < 7,
            vHour + 12,
            vHour
        ),
        Value(MinuteValue3.Selected.Value),
        0
    )
)

If this works, you can consider accepting this as well as it is a different question for people to find.

[MH3]

Sir @WarrenBelz  Where should I put that formula of yours?

and I fixed the 9:00 to 09:00 now it displays correctly but when I take 12:00 it displays 12:00 PM instead of 12:00 AM?

[WarrenBelz]

Hi @MH3 ,

In the Update of the card.

Also 12:00 am is midnight. Much debate has ensued on this, but Wikipedia says so (must be right . . .), so it is working as expected as far as Power Apps goes.

[MH3]

Hi @WarrenBelz 

No, I am talking about 12 Noon, Before 1 PM Afternoon, if I select 12:00 it shows 12:00 PM which is correct!!

With(
    {
        vHour: Value(
            Left(
                HourValue1.Selected.Value,
                2
            )
        )
    },
    DateValue1.SelectedDate + Time(
        If(
            vHour < 7,
            vHour + 12,
            vHour
        ),
        Value(MinuteValue1.Selected.Value),
        0
    )
)

Sir, what's your formula doing can you please explain? and why you are comparing vhour with <7 and if it's true then add +12?

for what reason?