Dec 19 2022 11:33 AM
Dec 19 2022 11:33 AM
I have a Check In app made in PowerApps that uses SharePoint as its data source. When certain buttons are clicked, the exact hour gets recorded into this list (Check In, Lunch Out, Lunch In, Check Out). The problem is its all recorded into the same column and now I need to calculate the total hours worked in the month for every employee. Is there a formula I can use in a calculated column that would help me?
Dec 20 2022 07:10 AM
@vinidegrandi presumably the column where the times are being recorded is a single line of text column with the string looking like 08:00 11:30 12:15 16:00. There are 2 ways you can separate the times into separate columns: JSON column formatting or a flow in Power Automate. I'll explain the flow approach.
The SharePoint list has the times in the single line of text column and you need to create date/time columns (include time) for CheckIn, LunchOut, LunchIn, Checkout and a calculated column for the hours with the formula =24*((LunchOut-CheckIn)+Checkout-LunchIn).
The flow will split the Times column into the 4 times and populate the 4 columns in the list.
The flow looks like this.
1) We'll manually trigger the flow then the first action get items to retrieve all the items in the list:
2) Add an apply to each, click in the Select an output field and select value from the dynamic content box that appears. Then add 2 Compose actions and select the column with the date in it for the first compose, and the column of times for the second compose.
3) For each item in the list we want to split the text into the 4 times. To do this add a compose action and enter the expression split(outputs('Times'), ' ') which splits on the space between the times and returns just the first time value (index 0):
4) Add another compose action. This time the expression will concatenate the outputs of the startdate compose, a space and the outputs of the index0 compose, It will format the date in ISO8601 format which is yyyy-MM-dd HH:mm. The full expression is
formatDateTime(concat(outputs('StartDate'),' ',outputs('Index0')),'yyyy-MM-dd HH:mm')
5) Copy these 2 compose actions to the clipboard and add them in 3 more times, changing the Index number to 1,2 or 3 as appropriate:
6) Finally you add an update item action and for the 4 date columns you select the outputs each result compose above.
Save and run your flow and it will populate the list items as shown at the beginning of this post.
If the date is in the same column as the times then you'll need to adjust the flow as you'll have an extra number.
Longer term I recommend you re-build the Power App so that each time is recorded in its own column in the list.
Microsoft Power Automate Community Super User