0
It is 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 just want the number of May days (result = 4)
1 answer
3
A date in Excel, also called the serial number, is the number of days that passes from 01-01-1900. So subtracting two dates and showing the formatted result as General or Number 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 dateless.
In doing B2-A2 gives you directly the difference in days of the two dates, which in case is 5. Then the function DIA gives you the day of the second date (June) which is 2, and by subtracting these 2, stays only with the days of May, 3.
If you want to count with your own starting day 28/5/2018 just add 1, transforming its formula into:
=B2-A2-DIA(B2)+1
Who will give you the 4.
-
For this case it is simple. But I need a formula that allows me in any difference of dates, take values per month.
-
For example, if in this case I wanted the days of June, that formula no longer served
-
@The Ricardosantos of June would be
=DIA(B2). Might even for aSEto be easy to exchange between getting the ones from the initial month or the final month, but I still suspect that’s not what you’re looking for. Then try to be a little clearer in the question, indicating which cases you have to cover. I would even advise you to give a few examples of dates and their results -
Thanks for the tip, 'cause I think I’m gonna make it.
-
When I have the solution I’ll put it here
-
But imagine for example 28/5 to 3/7 and wonder how many days are May, June and July
-
I’ll have to consider whether it’s a month of beginning or end or a half.
-
If half is total month, if end is day of month and if start is the difference between start and end - the difference of day 1 of the month following the end
-
@Ricardosantos I guess you still don’t know what you want, and without knowing that it’s hard to answer too. But it seems to me that now it tends to how many days stay in each month between two dates, which is already something different than what was originally in the question (although not difficult to do)
-
I want to count the days of one room a month.
-
The person checks in on 28/5 for example and leaves on 3/7. And I want to count the days of May (4), June (30) and July (3).
-
and I’m not getting it. In isolation it’s easy, but imagine a log table with a lot of data. I intend to know the quarter x how many days it was occupied in each month
-
Imagine a table with column A referring to the Check In Date, column B referring to the date of the last night, column C the number of the room and column D the Customer Name. I need to know month by month the occupation (number of nights). I’m really not getting it. Thanks for the help anyway. If you can find a solution, thank you. Ricardo Santos
-
@Ricardosantos Unfortunately this is a different question even with its nuances. I would also advise you to open up a new question, explaining as best you can what you want, so that you actually have the answer you want. And giving the necessary examples to make clear, and not forgetting the particular cases that may arise. Remember that the more explicit and clear you are, the more likely you are to have an answer to what you are actually looking for.
-
Isac, the question is the one I set out in the previous comment
-
I have a table with column A referring to the Check In Date, column B referring to the date of the last night, column C the number of the room and column D the Customer Name. I need to know month by month the occupation (number of nights).
-
Isac, thank you for everything
-
I managed to resolve the issue
-
I created 3 auxiliary columns, 1 with the number of months involved, another with the days of the initial month and another with the
-
with the days of the final month
-
dividing the problem made it easier to make the formulas
-
Month number column Formula =([@[Year Out]]-[@Year])*12+[@[Month Out]]-[@Month]
-
Column days start month Formula =SE([@[Month Output]]<>[@Month];FIMMÊS([@Date];0)-[@Date]+1;0)
-
Column final month Formula =SE([@[Month Departure]]<>[@Month];DAY([@[Last night]]);DAY([@[Last night]])-DAY([@Date])+1)
-
@Ricardosantos I’m glad you were able to solve the problem :)
Browser other questions tagged excel
You are not signed in. Login or sign up in order to post.
Isac, it’s 4 days. 28, 29, 30 and 31.
– Ricardo Santos
If you use the DAYS function, you give me the result of 6. And I want to know automatically how many May days are.
– Ricardo Santos
I expressed myself badly, I meant 29, 30 and 31 ? Because 28 will not normally be accounted for. Or you want to account for this too ?
– Isac
Yes. I want to tell this one too. It would normally be DAYS +1
– Ricardo Santos