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

Changing a column basing on the date

Hi,

Please, see the example table I attached first:

I have a gallery with 3 rows: Chocolate, Vanilla, and Strawberry.

If a person clicks on "Chocolade" at 10:00 PM on 6/28/2020, I would like the excel table to be updated in a row 'Chocolade' and column '6/28/2020 and it should be written "10:00" in it.
If a person clicks on "Strawberry at 4:25 PM on 7/02/2020,  I would like the excel table to be updated in a row 'Strawberry' and column '7/02/2020 and it should be written "16:25" in it.
etc.

Currently, I was using a "Patch" function, but I have no idea how to change the column basing on what day is today.

I hope it is understandable what I want to achieve, but I will appreciate if you have any other questions. Thank you for your time!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @pal95 :

I think I understand what you mean.

First,Your needs are not difficult to meet, but they are very complicated.

Because if you need to update the data of the specified column, you need to specify the column name. The column names cannot be replaced with formulas, which means that if there are 100 columns in the table, one hundred patch() formulas must be written.

Second,my idea is to adjust your data source to act as columns and columns as rows.

Because we can locate the specified line, which can save a lot of code.

18.gif

Add three buttons:

Chocolade:

OnSelect:

 

Patch(
    Table1,/*Table1 is my data source*/
    LookUp(
        Table1,
        Year(Check) = Year(Today()) && Month(Check) = Month(Today()) && Day(Check) = Day(Today())
    ),
    {
        Chocolate: Text(
            Now(),
            "[$-en]hh:mm"
        )
    }
)

 

Vanilla:

OnSelect:

 

Patch(Table1,LookUp(Table1,Year(Check)=Year(Today()) && Month(Check)=Month(Today()) && Day(Check)=Day(Today())),{Vanilla:Text(Now(),"[$-en]hh:mm")})

 

Strawberry:

OnSelect:

 

Patch(Table1,LookUp(Table1,Year(Check)=Year(Today()) && Month(Check)=Month(Today()) && Day(Check)=Day(Today())),{Strawberry:Text(Now(),"[$-en]hh:mm")})

 

2.JPG

1.JPG

Best Regards,

Bof

 

View solution in original post

6 REPLIES 6
JayMagness
Solution Specialist
Solution Specialist

Hi,

 

it would really help all if we had a final desired outcomes to help all get the desired results.

 

My thought is you would be better served with SharePoint and use created date and the Choice. this will allow you to get any number of output through PowerBI, or even as you wish with chunking date out as you wish.

 

Feel free to contact me directly if you want we can get on a teams session and I can try to help you thorough all of this.

 

 

Thank you for your reply.

I think it is really simple what I need:

If I press the button in the gallery (field 'chocolate') at this moment, I would like the app to find current day from the columns (so 6/22/2020) and write "2:22 PM" to a row named "chocolate" and column named "6/22/2020"

If I do exactly the same tomorrow, I would like to have "2:22 PM" written in a row named "chocolate" and a column named "6/23/2020".

As you can see, I have a column prepared for every single day.

Hi @pal95 :

I think I understand what you mean.

First,Your needs are not difficult to meet, but they are very complicated.

Because if you need to update the data of the specified column, you need to specify the column name. The column names cannot be replaced with formulas, which means that if there are 100 columns in the table, one hundred patch() formulas must be written.

Second,my idea is to adjust your data source to act as columns and columns as rows.

Because we can locate the specified line, which can save a lot of code.

18.gif

Add three buttons:

Chocolade:

OnSelect:

 

Patch(
    Table1,/*Table1 is my data source*/
    LookUp(
        Table1,
        Year(Check) = Year(Today()) && Month(Check) = Month(Today()) && Day(Check) = Day(Today())
    ),
    {
        Chocolate: Text(
            Now(),
            "[$-en]hh:mm"
        )
    }
)

 

Vanilla:

OnSelect:

 

Patch(Table1,LookUp(Table1,Year(Check)=Year(Today()) && Month(Check)=Month(Today()) && Day(Check)=Day(Today())),{Vanilla:Text(Now(),"[$-en]hh:mm")})

 

Strawberry:

OnSelect:

 

Patch(Table1,LookUp(Table1,Year(Check)=Year(Today()) && Month(Check)=Month(Today()) && Day(Check)=Day(Today())),{Strawberry:Text(Now(),"[$-en]hh:mm")})

 

2.JPG

1.JPG

Best Regards,

Bof

 

Thank you for a very detailed solution! I will try it and if it will work I will mark it as solved

Hi @pal95 :

Is your problem solved? Do you still need help?

If you have found a solution, could you mark it as a solution so that more people can see it.

Best Regards,

Bof

It's not exactly what it should be, but I can accept, I think it's the best possible solution.

Thank you!

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 (6,070)