How to Convert a Serial Number to ISO 8601 Date in Power Automate | 2022 Tutorial

hello everyone Michael back with another video in this video I'm going to show you how to convert a serial number to an ISO 8601 date in power automate if you enjoy power automate powerapps SharePoint teams tutorials feel free to subscribe because I'll be filling out more videos in those areas so let's get into it so the other day I was actually importing some Excel data into a SharePoint list now I was working with a few dates and I was running into this issue where I was trying to move these date values into a SharePoint list but they were appearing as serial numbers and so let me show you they're appearing as these numbers and because of that they weren't inputting correctly and I wasn't able to resolve it uh using any power automated actions so I had to use a custom expression so I will show you with that expression and what it looks like here all right so as you can see I have a short list of data and I'll be working with this date field and I already made my SharePoint list containing uh The Columns that I need so we got a few just single line of text and then we have one date column right here and so let me go into Power automate so I can show you how to convert the number all right so this is going to be an instant Cloud flow in my case because I'm just pressing a button taking it in the Excel data moving it into a SharePoint list so it'll the trigger will be manually trigger this flow and I will say Excel data to SharePoint lists and then uh I'll just say like dates at the end so I know what I'm doing all right so the first action I'm going to use will be ex I'll use an Excel action so I actually want to get the rows from the uh the Excel list so I want to be able to pull this data so I'll be using a list rows present in a table location so this file is actually located in my marketing SharePoint my favorite one uh the documents Library the file so I'm going to click on this folder so it shows me the document library and all the folders and files inside of it so this file I'm actually working with is test debt uh xlsx and I only have one table in here so it's going to be table one so the reason I use custom expression was because when I clicked on date format date time format down here you can actually choose serial number and ISO and it will convert that over but for some reason my Excel document kept sending the data over as as like a general number and I try going into more number formats and changing all the dates around it still wasn't working I was just getting fed up so I just decided to write a custom expression and uh it ended up working so for the daytime format here I'm just going to not include anything I'm pretty sure default serial number so if you're able to get it to work with ISO 860 8601 I'd go ahead and use that but if you aren't able to and you're not able to get it to work I'll show you the expression all right so let's go to create item all right so I want to create an item for each row in that Excel document so the side of the artist will be marketing and this is my test data SharePoint and when I use the dynamic content from list Rose press on the table it's going to put it into an apply to each and that is because each value right here we have to do this for every single row and since there's seven rows it's going to have to apply to each every Row in that Excel I know that took me a little while to learn why I had done that all the time and I was super scared but it all makes sense now because it tosses it into an array so the food the sports I'm just going to show you it failing right now so I just have birthday right here and it's going to try and import that serial number into the birthday date column and it's not going to allow that because it has to be formatted and it has to be sent over as a date and it's set over as like an integer of numbers so go ahead and press test going to ask me for the connectors and it should take about a few seconds so as you can see we have a failure here and I'm just waiting for the error message to pop up the create item uh is not valid import parameter by birthday is required to be a type string date the runtime value three two nine one four be converted doesn't have expected format so as you can see it didn't have the correct format so I'm going to show you right now the uh the format so we're just going to go ahead and you want to paste in the birthday and then press Ctrl a control C to copy that value so now that value will look like this and we're actually going to use that so I'm going to open up notepad alright so I have notepad open so I want to start writing my expression so we're going to do an if statement first and that's because if there's a null Value Pass from this so if this wasn't here and I tried to import that data it does the power automate doesn't know how to send over a uh a blank string over into a date field so you'll get another error in this if statement I'm writing will fix that so we're going to do if equals and let me see if I can zoom in right here f equals and then we're going to paste over the uh a dynamic content so it'll be the items applied to each in birthday and you want to remove the curly bracket and the add symbol so this equals it's actually checking to see if birthday is equal to this blank string so if this cell is blank it's going to return true if not it's going to return false and so we're back in the if statement so if the if this equals is true so if this is blank we want to return null because we can pass null from Power automate and uh SharePoint list will understand that hey there's no value there now we're doing if the birthday field is not equal to blank so there's a value there we're going to want to do format date time let me make this bigger so we want to do format daytime and then we want to do add days because basically what a serial number is is it's adding all the numbers up from 18.99 so it's adding all the days from after 1899 December 30th we're going to close this up and then we want to do int so we want to get the serial number and make it an integer so we can add it up on the add days function then we're just going to paste the the dynamic content that we have copied to the clipboard and zoom this out remove the curly brackets let me close this in statement up all right then we just want our format date time which is going to be year year month month day day and I will write this in the description so you don't have to uh copy it but I'm just trying to explain what's going on here and I believe I need one more parenthesis at the end just to close up the function so let's go back into Power automate we'll paste this expression and uh hopefully it's good to go it says no let's just check it out real quick all right so I just had an extra around to see right here that I had to remove for the add days function all right let's go ahead and save this and we'll go ahead and test it with uh manually recent run flow as you can see it was able to take the birthdays and import them successfully because it was actually a date field let's look at the output so from the list Rose present in data you can see that the birthday was the birthdays were all Sun as serial numbers but our function our custom expression that we wrote actually converted them to dates and we were able to import that into our SharePoint list so I'll be the video uh I'll put the put the custom expression in the description so you guys have it for easy use and hopefully this resolves some issues if you're running into that serial number to ISO 8601 so if you like the video feel free to comment like subscribe if you have any video ideas feel free to leave them in the just comments and I will catch you in the next one

Channel: Michael Alex

Views: 4,662

Keywords: Microsoft, Microsoft Forms, Forms, Microsoft SharePoint, SharePoint, Teams, Power Automate, Power Platform, Microsoft Flow, Flows, Automation, Tutorial, Guide, 2022, Microsoft Outlook, Outlook, Email, serial number to iso 8601, serial number, iso 8601 conversion, conversion, power automate, convert date, convert serial number

Id: NYxT_v-yQYQ

Length: 9min 7sec (547 seconds)

Published: Thu Dec 01 2022