Skip to main content

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

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.

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.

Reply


Terms & Conditions