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

[Gulshan_Sharma]

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

[Zollie]

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.

[Gulshan_Sharma · Accepted solution]

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.