Dec 25 2019 07:17 PM
Hi,
I need hep on sharepoint list time sheet and my setting here it is also i need the calculation format please and time entry like this 09:00 like that Thanks
Employee Name :person type
Date ,Start Time ,Lunch Start ,Lunch End Time ,Finish : date time type
Total Hours Worked : number type
Day Pay
Thanks
Dec 28 2019 04:52 AM
Hi @RAMOLE1325,
As far as I know, you cannot store time only. You can display the time portion of a date column in a calculated column using the following code:
=TEXT([Date Column Name],"hh:mm AM/PM")
I hope this helps.
Norm
Dec 30 2019 04:30 AM - edited Dec 30 2019 04:53 AM
Solution@RAMOLE1325 this can be done with a customised SharePoint form with Power Apps which I think is easier than trying to do it with calculations in the list itself.
The result is shown below and the form and list are also shown in the attached video.
I added a connection to Office365Users so we could select from all employees, but as there are limits on the number that can be returned we've added a text input field to enter a name to search for and a dropdown to select from the employees returned.
The date for each of the start, lunch start, lunch end and finish times as well as the hours and minutes for these needs to be entered/selected. In the Hours card there are then text input fields for the minutes in the morning and afternoon and the default for the Minutes (AM) is a DateDiff function:
..and a similar DateDiff function is used to calculate the minutes in the afternoon:
For the hours we then add the minutes in the morning to the minutes in the afternoon, divide by 60 and round to 2 decimal places:
The hours are then multiplied by the hourly rate which is a dropdown:
We've made a couple of the data fields not visible that write the field back to the SharePoint list. These are for the Title column where the Employee name will be saved, and the hourly rate data field. These both get their values from the Employee dropdown and the hourly rate dropdown using the following syntax:
I hope that gives you an idea of how you can achieve what you want. Come back if you've got any questions.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Dec 30 2019 04:34 PM - edited Dec 30 2019 10:20 PM
Hi @RobElliott
Thank you i fellow this and when selected the hours calculation in power app OK but in SharePoint it records wrong StartTime to Finish hours as you can see on screenshot please have a look where i make the mistake please
Thanks
Dec 31 2019 02:14 AM
@RAMOLE1325 could you post the screenshot please, it was not on your post. Thanks.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Dec 31 2019 02:24 AM
Dec 31 2019 02:53 AM
@RAMOLE1325 well done for getting to this stage. In each case the difference is 2 hours which indicates to me that the regional settings in your SharePoint site might not be set correctly. That's the first thing to check.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Dec 31 2019 02:27 PM - edited Dec 31 2019 02:31 PM
You correct it was the settings, now i corrected and its fine, thank you and your perfect teacher Rob,
Can you please tell whats
1)the fill color on your app.
2)second thing the size of the app please.
3)last thing say example if you select StartTime and now you jump to finishing without a break it will not calculate?
Thanks and happy new year.
Jan 01 2020 06:34 AM
Happy New Year to you as well.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Jan 01 2020 10:02 PM - edited Jan 01 2020 10:10 PM
No lunch sometimes we skip and eat dinner .
Thanks for your help,
1)but the only thing i dont understand is if you look the screen shot at bottom of the app is White i dont know why, do you know how to get rid of that white background please.
2) When i click add new timesheet the employee dropdown always starts with first employee, is there a way to start blank?
Thanks
Jan 02 2020 02:04 AM
Try the following:
1. Make sure your SharePoint form covers the full area of FormScreen1
2. Insert a combobox instead of a dropdown.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Dec 30 2019 04:30 AM - edited Dec 30 2019 04:53 AM
Solution@RAMOLE1325 this can be done with a customised SharePoint form with Power Apps which I think is easier than trying to do it with calculations in the list itself.
The result is shown below and the form and list are also shown in the attached video.
I added a connection to Office365Users so we could select from all employees, but as there are limits on the number that can be returned we've added a text input field to enter a name to search for and a dropdown to select from the employees returned.
The date for each of the start, lunch start, lunch end and finish times as well as the hours and minutes for these needs to be entered/selected. In the Hours card there are then text input fields for the minutes in the morning and afternoon and the default for the Minutes (AM) is a DateDiff function:
..and a similar DateDiff function is used to calculate the minutes in the afternoon:
For the hours we then add the minutes in the morning to the minutes in the afternoon, divide by 60 and round to 2 decimal places:
The hours are then multiplied by the hourly rate which is a dropdown:
We've made a couple of the data fields not visible that write the field back to the SharePoint list. These are for the Title column where the Employee name will be saved, and the hourly rate data field. These both get their values from the Employee dropdown and the hourly rate dropdown using the following syntax:
I hope that gives you an idea of how you can achieve what you want. Come back if you've got any questions.
Rob
Los Gallardos
Microsoft Power Automate Community Super User