cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
greenaal08
Regular Visitor

Gallery with weekly date without using excel formula

Currently am making a gallery that shows a weekly date an someone who has signed up to manage that week. I am currently using a dd-mmm-yyyy cell format in excel with a B2+7 formula in the column to make it weekly, however, powerapps does not accept excel sheets using formulas. My starting date is 04-Jan-2021 (column name 'Start Date') and I have over 20 rows automatically formatted thanks to the =B2+7 in each one. What's the best way to translate the weekly date column with from excel into powerapps using the dd-mmm-yyyy formatting style?



3 REPLIES 3
GarethPrisk
Resident Rockstar
Resident Rockstar

As you noted, you'll need to offload the transformation into Power Apps itself. If your Excel cell (B2 / column B), for the rows, as a date value. Then something like:

Text(
    DateAdd(
        ThisItem.colDate, // The reference to your column
        7,
        Days
    ),
    "dd-mmm-yyyy"
)
v-qiaqi-msft
Community Support
Community Support

Hi @greenaal08,

Based on the issue that you mentioned, do you want to format the date with "dd-mmm-yyyy"?

Could you please share a bit more about the scenario?

 

Actually, I think a General column in Excel may be the best method to achieve your need because that a date type column in Excel has difficulty displaying in Power Apps. A format column in Excel could not recognized in Power Apps, which means you should format the data column in Power Apps and save this format data back to the Excel table.

 

I have a test on my side, please take a try as below.

1). Format all the date columns in your Excel table as a General column.

2). Add a Gallery and set the Items property as your Excel table name

3). Set the End Date Label with Text property as below:

Text(
    DateAdd(
        DateValue(
            "04-Jan-2021",// Here please replace with your Start Date column(format it as General type!).
            "en-US"
        ),
        7
    ),
    "dd-mmm-yyyy"
)

vqiaqimsft_0-1635230613247.png

 

Best Regards,
Qi

hi @v-qiaqi-msft thanks for responding! I manually put the dates in their normal format in excel (so starting at 03-jan-2021 and manually added a week to each just so it showed up in the gallery(this was just to test it) which allowed it to show up in the gallery in powerapps. With your text is there a way where the text label that will duplicate in each new gallery entry will add 7 to the day? for now it just adds 7 to the start date

 

greenaal08_0-1635430232797.png

 

1 in this case is the manual from excel of which your use of Text(datasource, format) worked perfectly! 2 is your code, which works but not in the gallery format. I eventually want to make a form where I can add the next week in the date sequence and fill in a name of who is working that week, then it would add to the gallery. For that i would need the date to calculate itself per entry which is where I am currently stuck.

Sorry if the explanation is crude please let me know if you need clarification

 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,667)