cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Changing a column to date instead of text

Hello!

I have a flow which will create a table of an excel file and then puts all of the information inside of a sharepoint list. 

The excel file looks like this when it is being exported (It is not a table yet but does have the column name there already):

Column name (text)
Row (date)
Row (date)
Row(date)

So the column name is set to Text and the rows are a Date value. Whenever I use the function create table in my flow, it will turn the whole column into a text value as the column name is a text value instead of a date value. 

How could I change my flow to turn it into a date column? 

Create table function: https://gyazo.com/731188e6407c22e0521a4a81fd0d41cd
Creating the sharepoint list with the information of the excel file: https://gyazo.com/ed9853f60bd6b44d177119a993649b9a

20 REPLIES 20
Hardesh15
Super User
Super User

@Anonymous 

 

Use adddays function in expression-

addDays('1899-12-30',int(outputs('Compose')),'dd-MMM-yyyy')

 

outputs('Compose') : i used compose action in which i am giving input of excel column.

 

Thanks

Hardesh

 

 

Anonymous
Not applicable

Sorry I do not understand you, what do I need to put where? @Hardesh15 

@Anonymous 

 

Please follow below steps-

1. Just above create item, use a compose action.

2. supply your text column value from excel in compose input.

3. Use my formula (given in previous post) in create item start-date column.

 

Note: you can also use function via expression in start-date column.

 

Thanks

Hardesh

Anonymous
Not applicable

@Hardesh15 

What would the formule be in the expression of Start - Date? Because I do not have a compose one right now and you told me to put in this: 

I mean I need to put the column Start - Date nowhere?

 

addDays('1899-12-30',int(outputs('Compose')),'dd-MMM-yyyy')

 

@Anonymous 

if you do not want to add compose action no worries. Click in Start-Date column and select Expression from Dynamic window, add my given expression here.

Annotation 2020-07-21 115515.jpg

 

then remove

outputs('Compose')

and add your column dynamic value.

 

Thanks

Hardesh

Anonymous
Not applicable

Thank you so much! @Hardesh15  It works great!

 

 

Anonymous
Not applicable

Current output is good: 

TimoMigchielsen_0-1595315148387.png

But whenever I go on PowerApps and try to compare the values, I get no result, here is the formula: 

Start date is the value from the screenshot I sent above, I have made it not show the time, but otherwise it is 17:00, this might mess up this formula.

Filter(
        Planningtest;
         'Start - Date' = SelectedDate
    );

 This is how the SelectedDate is formatted, we might need to change the format from Start Date the same as this one:

TimoMigchielsen_1-1595315262470.png

 

@Anonymous 

 

you have seen last part of expression- dd-MM-yyyy. in this add hour and min as well. use this addDays('1899-12-30',int(outputs('Compose')),'dd-MMM-yyyy HH:mm') --> HH used for 24hour and if you want 12hr format use hh.

 

Thanks

Hardesh

Anonymous
Not applicable

@Hardesh15 

 

Then it is still being displayed like this: 2-7-2020 17:00 

The time needs to be 2-7-2020 00:00:00

This is the code I have tried: 

addDays('1899-12-30',int(items('Apply_to_each')?['Start - Date']),'dd-MMM-yyyy HH:mm:ss')


I then get this error, before it crashed the first 500 rows where formatted like I stated above:

TimoMigchielsen_0-1595316373607.png

 

@Anonymous 

 

Please check data in that column if it is blank/text then you need to add if condition in given expression. What about the rest of the items? are they in correct format? expand create item and post screen pls.

 

Thanks

Hardesh

Anonymous
Not applicable

It was working earlier but I had to change something and now it doesn't work anymore. What does this mean? @Hardesh15 

Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '2741': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

This is the code I am using:  

addDays('1899-12-30',int(items('Apply_to_each_2')?['Start - Date']),'dd-MMM-yyyy HH:mm')

 

Anonymous
Not applicable

@Hardesh15 

I have reached max private messages today, check the message above 🙂

@Anonymous 

 

This kind of error comes when some data type cannot be converted into int type. its better to check your whole start-date data column value.

 

int(items('Apply_to_each_2')?['Start - Date']

it is not able to convert your data type into int.

 

Thanks

Hardesh

Anonymous
Not applicable

So what should I replace the whole expression with? @Hardesh15 

@Anonymous 

first figure out what kind of data it is taking, if it is blank,Then we can put condition like if(empty(Start-Date),null,adddays(.......))

 

Thanks

Hardesh

Anonymous
Not applicable

Sorry @Hardesh15 

 

I am not good at using Power Automate, are you able to tell me exactly what to do so I can follow it step by step? 

@Anonymous 

 

See, you are creating SharePoint list item through flow. Now See your list and use count function in any share point list column. you will get to know how many items are created. After that go to your flow Apply to each loop and put that count number and do  click on next or previous in loop. In this way you will find which item failed to create item in list.

 

After that go to your excel sheet look that row and check start date column value

 

Thanks

Hardesh

Anonymous
Not applicable

Now I know what you mean. I know what the issue is now. I am create a table untill 1200 rows and I only have 977 rows with a date, cause the others are empty. I'll now try your other code

@Hardesh15 

Edit:

I tried to change it to this but I get an error, what am I doing wrong? 

if(empty(items('Apply_to_each_2')?['Start - Date']),null,addDays('1899-12-30', items('Apply_to_each_2')?['Start - Date'])
Anonymous
Not applicable

Check above @Hardesh15 

Replaced it with this, no errors, just waiting for it to finish the flow

 

if(empty('Start - Date'),null,addDays('1899-12-30', int(items('Apply_to_each_2')?['Start - Date'])))


Then getting this error:

Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '2741': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (4,030)