how to get value hour

(0) Share

Report

Posted on by AdhamFH

248

Hello,

i have an record in excel and it looks like this

and then i want to get a value one hour before endtime (2:00 PM) and display it in power apps how should i get it ?

would be appreciate your reply

Thank you.

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

FabianAckeret 975 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey @AdhamFH

You can import the Excel by using the Connector Import from Excel. Click on it, search the file, select the table and you're halfway there.
It now depends on how you'd want to display your Excel, but I simply assume you know that. Otherwise, just let me know.

To get 1 hour before the endtime, you can make use of the function DateAdd(). It needs 3 parameters
The Date/Time (in this case your Endtime from Excel)
The number of units that should be added (in your case it would be -1, since you want 1 hour before the endtime)
The unit (in your case Hours)

To make use of this, you could (assuming your imported table from Excel is called 'Table1') do something like

// in case you'd like the first entry of the Table1 DateAdd(First(Table1).Endtime,-1,Hours) // in case you're using it in a gallery DateAdd(ThisItem.Endtime,-1,Hours)

I hope this helps.
AdhamFH 248 on at

Like
a
(0)

Report
Copy link

Link copied!

hi @FabianAckeret
thank you for your reply, now i got the value an hour before endtime and be able to display it as booked for 2:00 PM in my gallery but i want set for 1:00 PM as "booked" also refer to my column start in excel, do you know how to achieve it ?

Thank you
FabianAckeret 975 on at

Like
a
(0)

Report
Copy link

Link copied!

Could you rephrase your question? I didn't quite get what you'd like to achieve.

Do you want to show the Start Item as 'booked' as well, when the Endtime is 'booked'?
AdhamFH 248 on at

Like
a
(0)

Report
Copy link

Link copied!

@FabianAckeret according to my excel here i have start and endtime column

and then in my gallery at power apps i want to set 1:00 PM and 2:00 PM is "booked" because 3:00 PM is endtime so another user can book it, im using this formula

if use this formula
LookUp(Table2,Ruangan = Dropdown1.Selected.Ruangan And Text(Date1,"[$-en-US]yyyyddmm") = Text(Today(),"[$-en-US]yyyymmdd") And Text(DateAdd(Start,TimeZoneOffset(Start),Minutes),DateTimeFormat.ShortTime) = Label1.Text ,"booked")
it will set 1:00 PM as booked but if modified the formula to add condition to get 2:00 PM with this formula below

LookUp(Table2,Ruangan = Dropdown1.Selected.Ruangan And Text(Date1,"[$-en-US]yyyyddmm") = Text(Today(),"[$-en-US]yyyymmdd") And Text(DateAdd(Endtime,TimeZoneOffset(Endtime) -60,Minutes),DateTimeFormat.ShortTime) = Label1.Text And Text(DateAdd(Start,TimeZoneOffset(Start),Minutes),DateTimeFormat.ShortTime) ,"booked")
it returns nothing

how should i do ?
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @AdhamFH :

Do you want the label to display "Booked" when at least one of these two conditions is met:

Text(DateAdd(Endtime,TimeZoneOffset()-60,Minutes),DateTimeFormat.ShortTime) = Label1.Text

Text(DateAdd(Start,TimeZoneOffset(),Minutes),DateTimeFormat.ShortTime) = Label1.Text

If so,please try this code:

LookUp(
Table2,
Ruangan = Dropdown1.Selected.Ruangan And
Text(Date1,"[$-en-US]yyyyddmm") = Text(Today(),"[$-en-US]yyyymmdd") And
(Text(DateAdd(Endtime,TimeZoneOffset()-60,Minutes),DateTimeFormat.ShortTime) = Label1.Text Or
Text(DateAdd(Start,TimeZoneOffset(),Minutes),DateTimeFormat.ShortTime) = Label1.Text),
"booked"
)

Best Regards,

Bof
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @AdhamFH :
Has the problem been solved?
Is there anything else I can help?
If my post helps, then please consider Accept it as the solution to help others.Thanks
Best Regards,
Bof