Update year of date to match the date

[Dan_K1]

Hello,

I have developed a flow that successfully imports excel data to a SharePoint list. The last step I need to complete is to update the year in the date column to match the day of the date. Basically when the user exports the excel sheet from its original data source, the source only gives the month and day in the date column, so excel assumes year is current year to fill in the blank. I know it sounds funny but stick with me!

For example, if I have a date in the date column that is 9/22/2023, I need to year to say 2022, not 2023. I know this change could take place within the excel sheet before import, but I am trying to save formatting steps for the end user.

So far in my flow I have a get items step which gets all the items I need to update, then an apply to each. Within the apply to each I have an update item step, and this is where I get stuck because I am not sure how to manipulate the date to update it as mentioned above.

Any help would be greatly appreciated!

[wskinnermctc]

So you just need to change the year from 2023 to 2022?

There are no dates that need to be 2021 or 1998 or anything? And you don't plan on running this next year or ongoing?

I have a SharePoint list which has a DateTime type column. If I wanted to subtract the year by 1, I would use the expression below.

concat(string(sub(int(formatDateTime(items('Apply_to_each')?['StatusUpdateDateTime'],'yyyy')),int(1))),'-',formatDateTime(items('Apply_to_each')?['StatusUpdateDateTime'],'MM-dd'))

This basically says, take the current datetime from the column and format formatDateTime() it to be just the year 'yyyy'. Convert that 'yyyy' to an integer/number int(). Subtract sub() 1 from that number. Then convert this into a text string(). 

Then formatDateTime() the column to just have the month and date 'MM-dd' which will be a text string. 

Concatenate concat() these two things together along with an extra '-' dash.

SharePoint List ExampleModify Item Flow

Since I have blanks in my SharePoint list, I needed to add the if() and empty() functions to check the field first. So it won't try to run the expression on the empty field and cause an error.

if(empty(items('Apply_to_each')?['StatusUpdateDateTime']),null,concat(string(sub(int(formatDateTime(items('Apply_to_each')?['StatusUpdateDateTime'],'yyyy')),int(1))),'-',formatDateTime(items('Apply_to_each')?['StatusUpdateDateTime'],'MM-dd')))

Let me know if this works for you 

[Dan_K1]

Thanks for your quick reply @wskinnermctc ! Your question, "There are no dates that need to be 2021 or 1998 or anything? And you don't plan on running this next year or ongoing?" is a very good point and one that I should clarify with the end user. I know we don't have data going back to years, however I believe there is data going back a few years at least.

I also believe I may have explained something incorrectly in my original post; However, I plan to meet with the end user tomorrow so I will gain more clarity on this topic and post back to this thread ASAP.

[wskinnermctc]

Yeah the first problem is that Excel data that doesn't have the year.

I don't know how you plan on interpreting the year when only having the Month and Day. 

Like I hard coded 1 year by subtracting 1. But that will just do it for all of the dates.

Honestly, the data should be corrected from the source output, (not saying modify it in the excel), like there should be an extra column or something when the excel is created. The user wouldn't have to format anything. The source output would provide the information.

I don't care how the source outputs the info, obviously a regular date column is ideal, but it could just have the year or whatever, but something to reference and use within power automate.

[Dan_K1]

@wskinnermctc Apologies for the delayed reply. I had my original explanation backwards. The original data source exports to excel with the month and two-digit year. Excel assumes the two-digit year is the day and uses the current year to fill in the blank to complete the date format. 

For my use case the date (day) doesn't matter. I am really only concerned with getting the month and year correct. So essentially, I want to keep the month that is given, hardcode the date to anything (say 01), but use the two-digit year as mentioned above and concat that with the first two digits of the current year.  Below is a snapshot of what I have tried to do along with the formula I tried to write for it (inspired by your formula from your previous post), but I am getting the error "String was not recognized as a valid DateTime."

I know this one is a crazy one and really do appreciate any further help you can provide on this! I do believe this use case is a one off.

formula in the AsOfDate of the apply to each:

concat(formatDateTime(items('Apply_to_each')?['AsOfDate'],'MM'),replace(items('Apply_to_each')?['AsOfDate'],formatDateTime(items('Apply_to_each')?['AsOfDate'],'dd'),'01'),replace(items('Apply_to_each')?['AsOfDate'],formatDateTime(items('Apply_to_each')?'AsOfDate'],'yyyy'),concat(variables('CurrentYrSubString'),substring(items('Apply_to_each')?['AsOfDate'],1,2))))

[wskinnermctc]

This isn't that crazy, but I need to see the data to understand better. Right now I can't tell what could be causing the error.

Can you put a Compose step in your flow before the Update Item. Put the AsOfDate into it without any changes into the Compose. Put a second Compose under it and put your Concat formula that you are using in the Update item. Run the flow and show me a couple of the outputs of the two separate compose. That way I can see the original value as well as what the Concat is doing. 

Also show a screenshot of the SharePoint list column with the date values.

I'm a little turned around about what values are being used, so if I can see them I could easily make an expression.

For a date to be accepted in SharePoint DateTime column it needs to be in the "yyyy-MM-dd" format. If you format a date as "MM-dd-yyyy" or "yyyy/MM/dd" or anything else other than "yyyy-MM-dd" will be an error since the column can't accept it.

[Dan_K1]

@wskinnermctc Thanks again for your reply! Out of respect for your time I hope I provided what you need first shot. Below are the compose steps you requested. First one is the date that comes in from the get items with nothing done to it. The second is the compose action that had my last expression in it. I had the get item step return only one item for troubleshooting purposes as I am working with a large data set.

Also, in the screen shot on the right is the AsOfDate column as it appears in my SharePoint list. You'll notice the first date reflects what is in the first compose action. Additionally, I took a screen shot of many dates to show that you will see dates (the 2 digit day) of 21 and 22. So, the year should show as 2022 or 2021, not 2023...no change to the month...date can be any day of the month (I would say any date of 1-28 since every month has 28 days it in at minimum).

[wskinnermctc · Accepted solution]

Ok, so I am using the dd day value to be the last two digits of the year. I will combine 20 with the dd so that it will be basically like 20dd.

Then I put the whole thing together with the 'MM-dd' from the original date. I keep the same dd value since it doesn't matter. If you want to change the dd, you could type in 01 or whatever you prefer.

Basic expression to create year:

concat('20',formatDateTime(variables('SPAsOfDate'),'dd'))

Full expression to make a date value (basic expression is inside this): 

concat(concat('20',formatDateTime(variables('SPAsOfDate'),'dd')),'-',formatDateTime(variables('SPAsOfDate'),'MM-dd'))

Here I am using a variable named SPAsOfDate in my example.

You only need to use the full expression, the first Compose is just to show how the year is made.

And the results format the date to be 2019 since the day date was the 19th.

The results are the date output with the year modified

Can you use that example in your apply to each and get the updates?

[Dan_K1]

@wskinnermctc Yes it worked, thank you! I also put the expression in the update item step and it updated the item on SharePoint. It appears I was on the right track but drastically over though the process. This has been a great learning experience as I am new to power automate. Auto sending email is about all that I have done until this. 

I will mark your response as the solution.

[wskinnermctc]

Always something new to learn. Just keep trying things and eventually it will be a lot easier once you know how things work together.