Time between two dates

[Sweedler]

Hello

I have a start date and an end date in column A and B respectively. Figuring out the number of days between them is easy enough, but I need to know which months the days fall in.

For example: START DATE: 18-JAN -- END DATE: 7-MAR

There are 49 days between those dates, but there are 13 days in Jan, 29 in Feb and 7 days in Mar. Does anyone know an easy way to calculate this. Or a complicated way, it doesn't matter.

 

[SanjayGMusafir]

Is this what you are looking for? Check this and revert -

Book1
	A	B	C	D	E	F
1	Start	End	Days
2	18-Jan	7-Mar	49	13	29	7
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2-A2
D2	D2	=IF(B2>EOMONTH(A2,0),EOMONTH(A2,0)-A2,B2-A2)
E2	E2	=IF(D2<C2,EOMONTH(A2,1)-EOMONTH(A2,0),0)
F2	F2	=IF(SUM(D2:E2)<C2,B2-EOMONTH(A2,1),0)

 

[Sweedler]

Is this what you are looking for? Check this and revert -

Book1
	A	B	C	D	E	F
1	Start	End	Days
2	18-Jan	7-Mar	49	13	29	7
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2-A2
D2	D2	=IF(B2>EOMONTH(A2,0),EOMONTH(A2,0)-A2,B2-A2)
E2	E2	=IF(D2<C2,EOMONTH(A2,1)-EOMONTH(A2,0),0)
F2	F2	=IF(SUM(D2:E2)<C2,B2-EOMONTH(A2,1),0)

Hello

I am having trouble making this work. Plus, I also need it to take only workdays into account

 

[SanjayGMusafir]

Plus, I also need it to take only workdays into account

There are 49 days between those dates, but there are 13 days in Jan, 29 in Feb and 7 days in Mar.

No where have you mentioned about Workdays in OP

I tried to deliver what you asked originally...

 

[Sweedler]

Hello

I realize that I neglected to mention WORKDAYS in my original post, but it is a necessity.

 

[Joe4]

Take a look at using the NETWORKDAYS function.

NETWORKDAYS function - Microsoft Support

Syntax: NETWORKDAYS(start_date, end_date, [holidays])

support.microsoft.com

I think you should be able to incorporate that into the formulas Sanjay posted.

 

[Sweedler]

Thank you both

¨With a little bit of patience and time I managed to get the formulas to work. Combining NETWORKDAYS with @SanjayGMusafir formulas

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[Joe4]

You are welcome. Glad we could help!