Use Excel & Power Automate | Record Working Hours from Phone & Update LinkedIn from Excel!

it's time to talk about power automate and excel power automate is a tool you already have access to if you have office 365 and it's even available in the free version of office but it's limited to what it can do now if you have no idea what power automate is i got you covered i have a step-by-step video that shows you how easy it is to use it and how much time you can save with it link is in the description right now we're going to combine this power with excel [Music] i'm going to show you two examples of useful automations that you can create with excel number one is to record your working hours by clicking a button on your phone so whenever you start to work you click a button and it's going to record the time and date to an excel table when you stop working just click that again to record the end of your work time the second automation that i'm going to show you will be triggered from excel this automation will create a linkedin post directly from your excel file so you select the row and you run the flow and your row will be turned into a post which will be live on linkedin we're going to set this up together it's really exciting stuff so let's start with recording our working hours before i create my automation my first step is to create a workbook where i'm going to be collecting the working hours so let's start off with a blank workbook now i need to have a table in there with the fields i want to document one is going to be date another one is going to be time and i also want to know if i've started the task or if i'm ending the task so i'm going to put type here now the second step is to turn this into an excel table let's just highlight this go to insert and select table you can also use the shortcut key ctrl t my table has headers and go with ok just to give this table a better name i'm going to go to table design and update the name to table time now because i'm creating a cloud flow i'm going to save this either on my onedrive or on sharepoint in this case i'm going to save it on drive let's use control s and save it under the folder called zed automate i'll give this a better name and click on save next step is to log into office.com go to apps and select power automate the flow that we're going to create is going to be triggered by us we can create it from scratch by going to create the instant cloud flow allows you to trigger this manually as you need so we're going to go with that give this flow a name i'll call it record working hours and we want to manually trigger this from our mobile so select this and click on create first step was added we have the ability to add additional inputs so when we click on the plus here you can add text input yes no file email number and date i want to go with yes no because i want to track whether i'm starting or ending my working hours so i'm going to update this to start and add a question mark and instead of please select yes or no let's update this to yes for start and no for end this way we know whether we're starting to work or we're ending our work next let's add a new step this time i want to add a new row to my excel files i need excel online for business my action is to add a row into a table which i can see right here so let's select that next step is to define the location of my file so just click on the drop down and select where it is in this case mine is onedrive for business the document library is onedrive and the file itself can be selected by clicking on the show picker icon selecting your folder so mine is in z automate and the file is right here next i can select the table that's in that file so power automate automatically scans for tables and it's noticed that there is a table called table time that's the one i need and immediately i get new fields these are my column headers in table time i added a date column a time column and a type column so for date what do we want to be in there i want it to be dynamic content and i want to record the date so the moment i click that button on my mobile the date should be recorded next for time i would like to have the timestamp which is another thing i can see under dynamic content so let's go with that type is what i added myself because i want to know whether i'm starting or ending my working day so i want to have this variable in here now i should see it under dynamic content already and if you happen not to see it you can just type it in here and then select it i'm going to go with start and that's it let's save this flow and test it out so you can test this out directly from here as well but it's more fun to test it directly from mobile so let's switch to my mobile app and let's run this flow to trigger the flow you need to go to the power automate app on your device now this is a free app and you can easily download it from the app store just go into it go to the button side and you should see your flow listed here select it it's asking us is it yes for start no for end yes i'm starting to record my hours i'm gonna go with that click on ok and done and now the flow started and my timestamp should be in my excel file so let's switch back to excel and see if it's there my file is right here let's see if we have everything recorded that's date that's time and the type so the type shows true if we're starting our working hours and it's going to be false if we go with no which means the data type for this is boolean now there are two things i would like to update here one is for the timestamp i just want to have the time and i want to have it in a readable format and for type i want to change the true and false to be start or end this is where you can use functions in power automate so let's go back to our flow and update this accordingly let's start with updating the type so here instead of true or false i want to see start or end this is where i can use a function or an expression and guess what power automate has the if function and this works very similar to excel we get to test for condition what we want if it's true and what we want if it's false now i want this condition to be based on my dynamic content so let's just switch my view to dynamic content and select start next what do we want if it's true well i want to see start and if it's false i want to see end now here we have to currently use single quotes for that so put start in single quotes otherwise end in single quotes and make sure the bracket is closed and okay we put our dynamic content inside the if function next for the timestamp here i want to format this to just hour and minute i can use an expression and if we just search for expressions that include the word format we can see format date time now here when we open the bracket we see that we need a string that contains the time and then the type of format that we want now sometimes when you switch dynamic content you can't see your variables here and it might not be obvious how to refer to them what you can do is just to hover over them so notice when i hover over timestamp i get this long code that's actually what i want when i hover over date that's what i get to be able to easily copy this name or this code i can switch to code view so click on the more options here and go to peak code the label i saw there was this one so i'm just going to copy it click on done and go back to writing my expression my expression was to format date time let's open bracket and paste in the code that i copied next is the format i want i'm going to put the format in single quotes i want hh colin m for hours and minutes make sure we have the bracket closed there and click on ok now i'm going to get rid of the timestamp okay so let's save this and let's test this one directly in here so go to test i'm gonna run it manually save and test here i can choose start or start is off which means it's end so i'm gonna leave it like this and run the flow click on done i should see this update here and be able to see the time it took for this to run now when i switch to my excel i already see it here i have my timestamp properly and i get end instead of false now you can easily run this from your mobile anytime you start your work just go to the app select the button and you'll have a date you'll have a time and you'll have the type you can then later create reports to analyze your working hours now let's quickly take a look at our second flow we're going to set this up the other way around so we are going to create a linkedin post directly from a row in an excel table i'm back in the power automate app i mean create and i'm going to create an instant cloud flow let's call this linkedin posts and this time my trigger isn't a button on the mobile app instead it's for a selected row in excel so i'm going to go with this and create the flow so first of all we need to define the file and the table that we're using to create our linkedin posts well this time i've already set it up so i've created an excel table that has two columns url and text so i want my linkedin post to include a link to this article on my site and i want it to have this text now this is formatted as a table and the table is called table posts the file is called linkedin posts and is saved on my onedrive for business so now let's go ahead and define the location it's onedrive for business the document library is onedrive my file let's go and find it it's in folder zed automate and it's called linkedin posts now when i click on the drop-down i'm going to see the tables in that file that's my table now i can also add any additional inputs that i want but in this case i don't need them i'm going to go ahead and add a new step let's search for linkedin we're going to go with the most recent version under actions i want to share a company update so let's go with that now at this point if you haven't authenticated yourself you will need to log in to linkedin with your email and password i've already done that so i can go ahead and select my company now these are automatically filled once you log in to linkedin for the content url well for this i'm gonna use dynamic content because remember my table has two columns url and text that's the one i want so for content url we're gonna go with dynamic content and i see it right here url formatted next is this visible to guest well let's go with yes now i also want to add some text to this so i'm going to go with advanced options and for text let's go with dynamic content again and select our second column which was text okay so all of this looks good click on save okay so now we're ready to go let's go ahead and run this flow so i've opened this file in excel online and if it's the first time you're working with flows in excel online you have to add it to your tab you can do that by going to insert office add-ins go to store and search for the add-in flow you can also type in power automate this should come up here it's microsoft flow for excel click on add now i'm going to continue and once it's installed you're going to see the flow option in the data tab now i'm going to select this one to post so just click somewhere that's on the same row you don't have to necessarily select the entire row and then click on flow now at this point you should see the flow that you just created and that's connected to this file here and that's the linkedin post so i'm gonna run the flow first time you run this you have to authenticate yourself and it's already done here so click on continue and run the flow so now we should see this post on linkedin okay so this is about dashboard tips let me bring up my other browser here i'm already logged into linkedin i'm on the excel plus page so let's refresh this to see if we get to see our post here and it's right here i hope this video gave you some ideas on how you can use power automate and combine it with excel a comment below and let me know what you think and also if you're currently using power automate let us know how you're using it if you like this video don't forget to hit that thumbs up and if you aren't subscribed to this channel consider subscribing so that we can get to see each other more often thank you for watching and i'll see you in the next video you

Channel: Leila Gharani

Views: 89,920

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, power automate, power automate tutorial, microsoft flow, power automate excel tutorial, power automate ui flow, power automate tutorial excel, power automate expressions if, power automate date formats

Id: YBi9PgbnfLQ

Length: 14min 33sec (873 seconds)

Published: Thu Mar 18 2021