Where could the error be in my time calculation formula?

Csuzi

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!

Find more posts tagged with

Accepted answers

All comments

Michael Culley

Where is the end time? I only see start times. Do they always work 8 hours a day or does it vary?

Csuzi

https://community.smartsheet.com/discussion/comment/346301#Comment_346301

@Michael Culley

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.

Michael Culley

@Csuzi

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)))

Michael Culley

That would be for the segedosziop column

Csuzi

Thank you very much for your help, but I typed it in and the result is not good.

The result is -8 hours.

Michael Culley

@Csuzi

It's supposed to be 24 +

csuzi.png

Change that to +

It works for one day, but the results are bad the next few days.

Michael Culley

Those are the correct results though right?

Michael Culley

If you work 8 am today and continue working until 4 pm the next day that’s 32 hours.

Csuzi

@Michael Culley

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.

Michael Culley

@Csuzi

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?

Csuzi

@Michael Culley

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.

Michael Culley

@Csuzi

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

Csuzi

@Michael Culley

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!🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.