cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdhamFH
Helper V
Helper V

how to get value hour

Hello,

i have an record in excel and it looks like this

test3.PNG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

6 REPLIES 6
FabianAckeret
Solution Sage
Solution Sage

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.

 


Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

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 ? 

test3.PNG

 

test4.PNG

 

 

 

 

 

Thank you

 

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


Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

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

test3.PNG

 

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

test4.PNG

 

 

 

 

 

how should i do ?

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

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,733)