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?
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" )
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" )
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
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
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
At the monthly call, connect with other leaders and find out how community makes your experience even better.