cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cfoenki
Frequent Visitor

Issue when collecting dates from an excel datasource

Hi all,

 

I'm struggling with an issue with my collections.

I have 2 collections I would like to merge (1 collection data after the other).

I found the correct code to do this but there is an issue with my dates columns. Somehow, the collection of the 2 excel tables does not retrieve the same date/time format which makes the merged collection impossible.

 

In the collection 1 the date/time format is: YYYY-MM-DDTHH:MM:SSZ

In the collection 2 the date/time format is: DD/MM/YYYY HH:MM

 

I don't know why it is different as in my excel source file the 2 tables columns have the same data type.

How could I fix this?

Is it possible to set a date/time format when collecting data?

I don't really need the time here, is there any way to collect only the date?

 

Thank you so much for your help.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @cfoenki ,

I cannot test this and you will need to duplicate this for the other collection, but this may allow both date/time fields to present identically. Note that the field name will be changed.

ClearCollect(
   CAPA_Collection;
   DropColumns(
      ShowColumns(
         AddColumns(
            YourFirstTableName;
            "CAPAPlanCreationDate"
            DateTimeValue(
               Text(
                  CAPA_Plan_Creation_Date;
                  ShortDateTime
               )
            )
         );    
         "Deviation_Reference";
         "CAPA_Plan_Reference";
         "CAPAPlanCreationDate"
      );
      CAPA_Plan_Creation_Date
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

7 REPLIES 7
poweractivate
Community Champion
Community Champion

@cfoenki 

 

Check if similar thread here works for you to adapt to your case:

 

How do I get my dataform to display dates in DD/MM/YYYY format?

 

To be more exact, from a Formula in the Power Apps Canvas App, you can use a Text function and a Format argument like this:

 

 https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/show-text-dates-times

 

and 

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-text

 

The Text function formats a number or a date/time value based on one of these types of arguments:

  • A predefined date/time format, which you specify by using the DateTimeFormat enumeration. For dates and times, this approach is preferred as it automatically adjusts to each user's language and region.
  • A custom format, which comprises a string of placeholders that define, for example, whether numbers show a decimal separator and dates show the full name of the month, the month as an abbreviation, or the month as a number. Power Apps supports a subset of the placeholders that Microsoft Excel does. In this string, the language placeholder specifies the language in which to interpret the other placeholders. If the custom format includes a period, for example, the language-format placeholder specifies whether the period is a decimal separator (ja-JP) or a thousands separator (es-ES).
...

Text( NumberOrDateTime, DateTimeFormatEnum [, ResultLanguageTag ] )

  • NumberOrDateTime - Required. The number or the date/time value to format.
  • DateTimeFormat - Required. A member of the DateTimeFormat enumeration.
  • ResultLanguageTag - Optional. The language tag to use for the result text. By default, the language of the current user is used.

or

 

Text( NumberOrDateTime, CustomFormat [, ResultLanguageTag ] )

  • Number - Required. The number or the date/time value to format.
  • CustomFormat - Required. One or more placeholders enclosed in double quotation marks.
  • ResultLanguageTag - Optional. The language tag to use for the result text. By default, the language of the current user is used.

 

 

 


Check if above helps @cfoenki 

cfoenki
Frequent Visitor

Thank you for the quick answer.

I'm not sure how I can use the Text function inside the collect function.

Can you help?

 

Here is my collection code (simplified):

ClearCollect(CAPA_Collection4;
   ShowColumns(CAPA_Collection; "Deviation_Reference";"CAPA_Plan_Reference";"CAPA_Plan_Creation_Date";
   ShowColumns(CAPA_Collection2; "Deviation_Reference";"CAPA_Plan_Reference";"CAPA_Plan_Creation_Date")
)

Hi @cfoenki ,

I cannot test this and you will need to duplicate this for the other collection, but this may allow both date/time fields to present identically. Note that the field name will be changed.

ClearCollect(
   CAPA_Collection;
   DropColumns(
      ShowColumns(
         AddColumns(
            YourFirstTableName;
            "CAPAPlanCreationDate"
            DateTimeValue(
               Text(
                  CAPA_Plan_Creation_Date;
                  ShortDateTime
               )
            )
         );    
         "Deviation_Reference";
         "CAPA_Plan_Reference";
         "CAPAPlanCreationDate"
      );
      CAPA_Plan_Creation_Date
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

WarrenBelz
Super User
Super User

Hi @cfoenki ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

funkyfoenky
Regular Visitor

@WarrenBelz 

Sorry, with christmas celebrations I didn't have time to test your solution. I will check on Monday and let you know.

Thanks!

Note: it's cfoenki, don't know why my login changed ^^

OK @funkyfoenky or @cfoenki ,

I will respond to either on this thread if required.

cfoenki
Frequent Visitor

@WarrenBelz  Sorry for the late reply.

I just tested your solution and it works, thank you so much! 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,388)