Forum Discussion

excel101's avatar
excel101
Copper Contributor
Jun 21, 2019
Solved

Date calculation formula needed for daily pickup

Hello Experts, I am looking for a formula that calculates daily numbers from check-in and check-out dates. I am aware of the basic formula that calculates the total number of days between two dates ...
Formulas and Functions
Twifoo's avatar
Twifoo
Silver Contributor
Jun 21, 2019
It appears that the check-in date is included in, while the check-out date is excluded from, the calculation of total nights. Thus, the formula in E2, copied down rows and across columns, is:
=IF((E$1>=$A2)*(E$1<$B2),
1,””)
  • excel101's avatar
    excel101
    Copper Contributor
    Jun 21, 2019

    TwifooHow can I use the formula you provided with a different date range? I tried to do it with a different date range but it is not working.

     

    Thank you again

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      Jun 21, 2019
      It depends upon the layout of your data. The formula always conforms to such layout but the logic would remain the same.
      • excel101's avatar
        excel101
        Copper Contributor
        Jun 24, 2019

        Twifoo Twifoo Thank you! Can you elaborate a bit further please. I have used the same formula but it just not working. What do you mean by "data layout" exactly? Do mind looking at the attachment with the new set of dates and the formula please? Thank you again.

  • excel101's avatar
    excel101
    Copper Contributor
    Jun 21, 2019

    TwifooThank you so much! It worked!

Resources