Jun 21 2019 08:53 AM
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!
Jun 21 2019 09:05 AM
Jun 21 2019 09:19 AM
Jun 21 2019 11:26 AM
@TwifooThank you so much! It worked!
Jun 21 2019 01:03 PM
@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
Jun 21 2019 07:12 PM
Jun 24 2019 06:24 AM
Jun 24 2019 10:15 AM
Formula works (attached), what exactly is wrong?
Jun 24 2019 12:20 PM
@Sergei BaklanThank you! One thing I noticed is that when I copied the dates and formula that you sent back and pasted into a new spreadsheet, the formula did not work. But when I re-typed one of the check-in/check-out dates the formula worked all of a sudden. It's like the the dates were not recognized until I re-typed them manually. Do you know what that could be? Thanks again!
Jun 24 2019 02:41 PM
Perhaps you didn't fix the row with absolute reference or like. Most probably wrong combination of absolute/relative references in pasted formulas.