Where could the error be in my time calculation formula?
Hello, I made a timesheet and wage calculation table.
The data is sent by employees using a form.
I have a problem with the time calculation.🙄
Here is the sheet.
Below is what I don't understand and I don't know where the error is.
I would be grateful if someone could check it. Thanks!
Answers
-
Where is the end time? I only see start times. Do they always work 8 hours a day or does it vary?
-
Sorry, but I messed up the example in my previous post. It shows the end time incorrectly. Below is the corrected one.
Not just 8 hours a day. In fact, there are cases where they work from 8:00 p.m. to 4:00 a.m.
-
Does this work as a simpler solution?
=([end date] - [start date]) * 24 + (VALUE(LEFT([end time], FIND(":", [end time]) - 1)) - VALUE(LEFT([start time], FIND(":", [start time]) - 1)))
-
That would be for the segedosziop column
-
Thank you very much for your help, but I typed it in and the result is not good.
The result is -8 hours.
-
It's supposed to be 24 +
-
Change that to +
-
-
Those are the correct results though right?
-
If you work 8 am today and continue working until 4 pm the next day that’s 32 hours.
-
Yes, you are right. It should work for me if the user worked from 8:00 a.m. to 4:00 p.m. on October 3rd and 4th.
-
So it looks like your original formula worked just fine. Your original formula gave you 32 hours which is correct.
Are you still thinking it's not correct?
-
you're right. However, my problem is that;
The worker writes in the form that he worked from 8 a.m. to 4 p.m. every day from October 3rd to 4th. The result should therefore be 2x8, i.e. 16h.
Sorry for my bad English! Maybe I can't articulate my problem well.
-
That should look like this :
Start date: 10-3-22, Start Time: 8:00
End date 10-3-22, End Time 16:00
Then the next day they would do
Start date: 10-4-22, Start Time: 8:00
End date 10-4-22, End Time 16:00
-
yes, that's what I came up with. I thought there was some trick that also takes shifts into account.
Thank you very much for the help!! I'm thankful!🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!