SOLVED

Timesheet

Copper Contributor

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 

13 Replies

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

best response confirmed by RAMOLE1325 (Copper Contributor)
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. 

PA-form.png

 

The result  is shown below and the form and list are also shown in the attached video.

 

0-SP-list.png

 

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. 

txtEmployee.png

 

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:


DateDiff-AM.png

 

 ..and a similar DateDiff function is used to calculate the minutes in the afternoon:

 

DateDiff-PM.png

 

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:

Hours.png

 

The hours are then multiplied by the hourly rate which is a dropdown:

 

DayTotal.png

 

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: 

hiddenDataFields.png

 

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

 

@RobElliott great response!

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 

@RAMOLE1325  could you post the screenshot please, it was not on your post. Thanks.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Hi
Sorry I forget to attach, please closely look the times the one on SharePoint is wrong

Thanks

@RAMOLE1325  there's still no screenshot :)

hi @RobElliott 

 

8B40702D-5727-418A-9374-C139B3D851ED.jpeg7F138592-F8E9-418B-B890-0CE6E15DFD4C.jpeg

@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.

 

regionalSettings.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Hi@RobElliott 

 

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.

@RAMOLE1325 

 

  1. the fill color is hex 588181 (RGBA 88,129,129,100)
  2. the size is 1185 x 690
  3. that's correct, it won't calculate anything but you can just put in exactly the same time for the lunch start and lunch end and it will do the calculation correctly. I didn't want to make the calculations too complex.

No lunch!No lunch!

 

Happy New Year to you as well.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

 

 

Hi@RobElliott 

No lunch sometimes we skip and eat dinner :cryingwithlaughter:.

 

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 issue.png

@RAMOLE1325 

 

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

1 best response

Accepted Solutions
best response confirmed by RAMOLE1325 (Copper Contributor)
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. 

PA-form.png

 

The result  is shown below and the form and list are also shown in the attached video.

 

0-SP-list.png

 

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. 

txtEmployee.png

 

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:


DateDiff-AM.png

 

 ..and a similar DateDiff function is used to calculate the minutes in the afternoon:

 

DateDiff-PM.png

 

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:

Hours.png

 

The hours are then multiplied by the hourly rate which is a dropdown:

 

DayTotal.png

 

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: 

hiddenDataFields.png

 

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

 

View solution in original post