View original (French)

Number of nights between two dates

Solved
Elowine Posted messages 18 Registration date   Status Member Last intervention   -  
Elowine Posted messages 18 Registration date   Status Member 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 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor 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
    1. Elowine Posted messages 18 Registration date   Status Member Last intervention  
       
      thanks, simple and it works too!
      cool
      0
      1. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482 > Elowine Posted messages 18 Registration date   Status Member 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
    2. Elowine Posted messages 18 Registration date   Status Member 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
      1. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482 > Elowine Posted messages 18 Registration date   Status Member Last intervention  
         
        If that were the case, I wouldn't have bothered to give you these explanations...
        0
    3. Elowine Posted messages 18 Registration date   Status Member Last intervention  
       
      So cool! I won't hesitate because it's really convenient.
      Thank you, have a good day.
      0