# Calculating days between two dates that's falling in a month

Topic Labels: Formulas
Solved
1652 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hi Guys,

I need help in developing a formula to calculate days between two dates that’s falling in a particular month.

For Example - I am working on task for 3 hrs a day that started on 8th Mar 2020 and continued till 12th Jun 2020. I want to calculate no. of hrs in Mar, April, May and Jun in separate columns.

In excel, I can directly derive these value through this formula:
=if(max(min(< Task End Date>,< Month End Date>)-Max(< Task Start Date>,< Month Start Date>,0))>0,NETWORKDAYS.intl(max(< Month Start Date>,< Task Start Date>),min(< Task End Date>,< Month End Date>),1,< List of Holidays>),0)<No. of Hours

1 Solution

Accepted Solutions
4 - Data Explorer

Hey Guys, I went through this again and was able to resolve the to some extend.

I am yet to validate all the results, but here is the solutions that I created:

IF(
{Start Date}>'2020-03-31',
0,
IF(
{Start Date}<'2020-03-01',
IF(
{End Date}<'2020-03-01',
0,
IF(
{End Date}<='2020-03-31',
WORKDAY_DIFF('2020-03-01',{End Date},'2020-01-01'),
WORKDAY_DIFF('2020-03-01','2020-03-31','2020-01-01')
)
),
IF(
{End Date}<='2020-03-31',
WORKDAY_DIFF({Start Date},{End Date},'2020-01-01'),
IF(
{Start Date}>'2020-03-31',
0,
WORKDAY_DIFF({Start Date},'2020-03-31','2020-01-01')
)
)
)
)

Here, I am calculating workdays in Mar. I am yet to update the list of holidays in this formula and kept it ‘2020-01-01’ for time being.

2 Replies 2
10 - Mercury

DATETIME_DIFF may be part of your answer. Checkout the date formula functions here, or, if no one else posts a solution, re-post with a work offered tag.

4 - Data Explorer

Hey Guys, I went through this again and was able to resolve the to some extend.

I am yet to validate all the results, but here is the solutions that I created:

IF(
{Start Date}>'2020-03-31',
0,
IF(
{Start Date}<'2020-03-01',
IF(
{End Date}<'2020-03-01',
0,
IF(
{End Date}<='2020-03-31',
WORKDAY_DIFF('2020-03-01',{End Date},'2020-01-01'),
WORKDAY_DIFF('2020-03-01','2020-03-31','2020-01-01')
)
),
IF(
{End Date}<='2020-03-31',
WORKDAY_DIFF({Start Date},{End Date},'2020-01-01'),
IF(
{Start Date}>'2020-03-31',
0,
WORKDAY_DIFF({Start Date},'2020-03-31','2020-01-01')
)
)
)
)

Here, I am calculating workdays in Mar. I am yet to update the list of holidays in this formula and kept it ‘2020-01-01’ for time being.