cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimoMigchielsen
Post Prodigy
Post Prodigy

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

@TimoMigchielsen 

 

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

 

 

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

@TimoMigchielsen 

 

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

@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')

 

@TimoMigchielsen 

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

Thank you so much! @Hardesh15  It works great!

 

 

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

 

@TimoMigchielsen 

 

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

@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

 

@TimoMigchielsen 

 

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

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')

 

@Hardesh15 

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

@TimoMigchielsen 

 

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

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

@TimoMigchielsen 

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

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? 

@TimoMigchielsen 

 

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

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'])

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
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.

Users online (1,988)