0
I have 2 dates 1.7.2016 and 5.12.2016, and I need to calculate number of days from month with 31 day (31+31+31+4=98).
How can I get the number of days?
Jadran
Posted 2018-01-30T21:58:09.337
Reputation: 11
How do I calculate the difference between dates?
Answers
4
How do I calculate the difference between dates?
Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in a cell, and an end date in another. Then type a formula like one of the following.
In this example, the start date is in cell D9, and the end date is in E9. The formula is in F9. The “d” returns the number of full days between the two dates.
DavidPostill
Posted 2018-01-30T21:58:09.337
Reputation: 118 938
0
Use SUMPRODUCT to iterate through the dates and sum up the ones that fall in the months with 31 days:
=SUMPRODUCT(--(DAY(EOMONTH(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),0))=31))
ROW(INDEX(A:A,A1):INDEX(A:A,B1)) creates an array of the dates from the start to the finish.
EOMONTH(...,0) finds the last date of that month.
DAY(...) returns the numerical day.
(...=31) tests if that day is 31. If so it resolves to TRUE otherwise FALSE.
-- turns the TRUE/FALSE into 1/0 respectively.
SUMPRODUCT(...) Cause the formula to iterate through the array above testing each date in turn and adding the 1s and 0s together.
Scott Craner
Posted 2018-01-30T21:58:09.337
Reputation: 16 128
Thanks Scott. This formula je ok but can you tel me what is (A:A in formula? – Jadran – 2018-01-30T22:58:31.260
A:A is just a place holder, it can be any column. It does not mater as long as it is a full column. We are returning a row number and not a physical value. So it does not mater which column is used. It could be XAA:XAA and still work. – Scott Craner – 2018-01-30T23:02:42.727
Remember that if this worked for you to mark as correct by clicking the check mark by the answer. – Scott Craner – 2018-01-30T23:05:30.140
I have no idea how to form a formula – Jadran – 2018-01-30T22:10:02.830
how to calculate diff between two dates in excel - Google Search – DavidPostill – 2018-01-30T22:12:20.877
I need only number of days from month who have 31 days (7, 8, 10, and 12 month) from two dates 1.7.2016 to 5.12.2016 – Jadran – 2018-01-30T22:26:52.523
Where does the
+4come in? – Scott Craner – 2018-01-30T22:44:32.330+4 come from 12 month – Jadran – 2018-01-30T22:46:11.997
so if the ending date was 15.11.2016 would you add 14 or 0 because it is in a month with only 30 days? – Scott Craner – 2018-01-30T22:48:00.130
I have two dates : 1.7.2016 and 5.12.2016. Diference is 157 days, but i need only days from month who have 31 day. 7 month=31 day, 8 month=31 day, 10 month= 31 day and in 12 mont=4 day. In total = 98. How i can get it ? – Jadran – 2018-01-30T22:53:40.937