how to get value hour

[AdhamFH]

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.

[FabianAckeret]

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]

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]

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]

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

[v-bofeng-msft · Accepted solution]

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]

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