View original (French)

Number of nights between two dates

Solved
Elowine Posted messages 18 Registration date   Status Membre Last intervention   -  
Elowine Posted messages 18 Registration date   Status Membre Last intervention   -
Hello
could you please tell me the formula to find the number of hotel nights between two dates?
cell A1 05/1/03/2015 arrival date
cell B2 07/03/2015 departure date that is 2 nights
cell C3 number of nights

thank you in advance

2 réponses

Réponse 1 / 2
eriiic Posted messages 24581 Registration date   Status Contributeur Last intervention   7 281
 
Hello,

=B2-A1
cell in Standard format
eric

--
By trying continuously, we eventually succeed.
So the more it fails, the more chances we have that it will work. (the Shadoks)
Besides saying thank you (yes, really, it’s a thing!!!), remember to mark it as resolved. Thank you
8
Elowine Posted messages 18 Registration date   Status Membre Last intervention  
 
thanks, simple and it works too!
cool
0
Raymond PENTIER Posted messages 58546 Registration date   Status Contributeur Last intervention   17 474 > Elowine Posted messages 18 Registration date   Status Membre Last intervention  
 
And why didn't you try this before asking for help?
You should know that dates are numeric values; it's the display format that's specific.
In B2 you entered 07/03/2015; but if you set the cell to standard format, it shows 42070, which is the number of days since January 1, 1900, the starting point of Excel's calendar.
And since in A1 the display in standard format is 42068, the difference in dates is a difference of these two numbers 42070-42068=2!

To satisfy your curiosity, I'm going to reveal to you that
it's the same for times, but with decimal numbers:
0.50 in standard format is 12:00:00 in time format, which is exactly noon
0.66 in standard format is 15:50:24 in time format, which is 3:50:24 PM
this explains why 0.50+0.66 results in 1.16, meaning one day, 3 hours, 50 minutes, and 24 seconds.
0
Elowine Posted messages 18 Registration date   Status Membre Last intervention  
 
Hello Raymond,
Thank you for these explanations! You know, I’ve never really had to use Excel except to create a basic little table and nothing more. What may seem obvious is not for me. I probably look ridiculous, and I hope it doesn’t bother anyone.
0
Raymond PENTIER Posted messages 58546 Registration date   Status Contributeur Last intervention   17 474 > Elowine Posted messages 18 Registration date   Status Membre Last intervention  
 
If that were the case, I wouldn't have bothered to give you these explanations...
0
Elowine Posted messages 18 Registration date   Status Membre Last intervention  
 
So cool! I won't hesitate because it's really convenient.
Thank you, have a good day.
0