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 but what I need is a formula that breaks down the total number of days day by day.
For example: check-in: 5/1/10 Check-out: 5/5/19 equals 4 days. Now I need to break down the 4 days by the day like this:
5/1/19     5/2/19      5/3/19       5/4/19
    1              1               1               1
Is there a formula that pulls from the check-in and check-out columns and automatically calculates the daily breakdown as shown above in separate columns? Attached is the file with process I need help with .
Thank you for your help!

Formulas and Functions

10 Replies

  • 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.

Resources