Difference between dates with condition (only count certain month)

Question

Difference between dates with condition (only count certain month)

Navigation

#1 by (3 votes)

0

Is it possible to calculate the number of days between two dates of only one month?

For example 28/5/2018 to 2/6/2018 and I only want the number of days in May (result = 4)

excel

asked by anonymous 02.06.2018 / 10:39

1 answer

Add MySQL column with increment not to repeat How to close the program by pressing the

score 3 · Answer 1

An Excel date, also called a serial number, is the number of days that go from 01-01-1900 . So by subtracting two dates and showing the result formatted as General or Numero already gives you the difference of days. If you want to get only the days of the first month, you need to subtract the days of the second month.

A little ASCII art to demonstrate:

   |       A       |        B      |        C         |
-------------------------------------------------------
1  |  Dia inicial  |   Dia Final   |  Diff no 1º mes  |
-------------------------------------------------------
2  |   28/5/2018   |    2/6/2018   |  =B2-A2-DIA(B2)  |
-------------------------------------------------------

The formula is =B2-A2-DIA(B2) formatted in general / number and no date .

By doing B2-A2 it gives you the difference in days of the two dates, which in this case is 5 . Then the DIA function gives you the day of the second date (June) which is 2 , and subtracting those 2 , is only with the days of May, 3 .

If you want to count with your own start day 28/5/2018 simply add 1 , turning your formula into:

=B2-A2-DIA(B2)+1

That will give you the 4 .