Skip to main content
Solved

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

G

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

Best answer by Gulshan_Sharma

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.

View original
Did this topic help you find an answer to your question?

2 replies

Z
  • Zollie
  • Inspiring
  • 254 replies
  • April 15, 2020

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.

G
  • Gulshan_Sharma
  • Author
  • New Participant
  • 1 reply
  • Answer
  • May 1, 2020

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.

V
1 person likes this

Reply


Most helpful members this week

Terms & Conditions