Need excel formula to calculate the number of days in a date range, if they end within that range
October 1, 2009 7:15 AM Subscribe
I need a formula to help calculate the number of days as compared to a range.
Importing a CSV for a query to show bookings within the date range of 9/1 to 9/30. Some bookings are booked through the entire month and some are partial. I need to figure out the number of days or percentage for the bookings that don't run all the way through the query range.
posted by malcommc to Work & Money (9 answers total)
Query Start Date: 09/01/09
Query End Date: 09/30/09
Booking1 | 02/14/09 (booking start) | 10/14/09 (booking end)
Booking2 | 03/01/09 (booking start) | 09/15/09 (booking end)
Booking3 | 06/11/09 (booking start) | 12/31/09 (booking end)
So I know that Booking 1 and 3 go through the entire month of the query range (30 days), but I need a formula to figure out how many days are in Booking 2. I know that you can apply formula to each row to subtract dates, but I have hundreds of rows. I was thinking it would read something like: "If end date is less than query end date, then subtract booking end date from query start date (and add 1)." Maybe to take it further, "else sum is 30 days"
Any help is appreciated.