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

xls Get items date issue writing to SP list

Users update an excel metric file and put a date for the period.  Having an issue controlling whether the users put in a TEXT formatted date or the excel Date format, which passes the dreaded "days from 1900" number. I am trying to use the following expression to update a SP list with "create item", which seems like it should work perfectly, but it says the expression is not valid.  Open to any ideas here:

 

 

if(contains(items('Apply_to_Each_2')?['Period']),'/'),

items('Apply_to_Each_2')?['Period']),

formatDateTime(addDays('12-30-1899',int(items('Apply_to_Each_2')?['Period']),'MM-dd-yyyy'))

 

 

BTW, I was able to use the formatDateTime on its own and it works, so the issue seems to be either the IF or CONTAINS functions.
 
Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
Gopala_Krishna
Super User
Super User

@Zed_Yer 

contains(items('Apply_to_Each_2')?['Period']),'/')

The above formula has an additional closing ) contains require two input parameters the string and the value to search on with the additional closing ) you are passing only string and no search value due to which the formula is not working as expected.

 

Remove the closing ) after the ['period'] and your formula will start working.

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if the information shared helps you, please consider giving a thumbs up 👍 and mark solution as resolved

  

View solution in original post

2 REPLIES 2
Gopala_Krishna
Super User
Super User

@Zed_Yer 

contains(items('Apply_to_Each_2')?['Period']),'/')

The above formula has an additional closing ) contains require two input parameters the string and the value to search on with the additional closing ) you are passing only string and no search value due to which the formula is not working as expected.

 

Remove the closing ) after the ['period'] and your formula will start working.

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if the information shared helps you, please consider giving a thumbs up 👍 and mark solution as resolved

  

View solution in original post

I had it wrong in two places, plus found another issue on the formatDateTime. Appreciate you for finding that and pointing it out.  To anyone else who needs the final working expression inside an Apply To Each loop, see below:

 

if(contains(items('Apply_to_Each_2')?['Period'],'/'),items('Apply_to_Each_2')?['Period'],formatDateTime(addDays('12-30-1899',add(int(items('Apply_to_Each_2')?['Period']),+0)),'MM-dd-yyyy'))

 

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (4,361)