How to calculate duration based on workdays, and deduct 0.5 days

Hello, I would like to track planned leave and duration for my team. On my sheet, I have "Start Date", "Half Day Start", "End Date"; and "Half Day End" columns.

If Start Date is Mon 24-Jun-2024 and Half Day Start is Ticked; and

End Date is Wed 26-Jun-2024 and Half Day End is Ticked, the Duration should be 2 days

I am using this formula

=NETDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = "Yes", -0.5, 0) + IF([Half Day End]@row = "Yes", -0.5, 0)

but the duration is showing as 3d, which is incorrect. How can I fix this please?

Find more posts tagged with

Formulas

Accepted answers

Mark.poole

@JGei

The check box is binary. So change the "Yes" to a 1. see how that helps.

=NETDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = 1, -0.5, 0) + IF([Half Day End]@row = 1, -0.5, 0)

How ever if you run into an issue that includes weekends I would use NETWORKDAYS instead.

=NETWORKDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = 1, -0.5, 0) + IF([Half Day End]@row = 1, -0.5, 0)

If you want to be able to do a column formula. Change your column from duration to text/number and at the end of your formula put +"d".

The Image below is using the text/number columns instead of duration columns.

All comments

Mark.poole

@JGei

The check box is binary. So change the "Yes" to a 1. see how that helps.

=NETDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = 1, -0.5, 0) + IF([Half Day End]@row = 1, -0.5, 0)

How ever if you run into an issue that includes weekends I would use NETWORKDAYS instead.

=NETWORKDAYS([Start Date]@row, [End Date]@row) + IF([Half Day Start]@row = 1, -0.5, 0) + IF([Half Day End]@row = 1, -0.5, 0)

If you want to be able to do a column formula. Change your column from duration to text/number and at the end of your formula put +"d".

The Image below is using the text/number columns instead of duration columns.

JGei

Thank you very much! Problem solved!

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.