Counting StayDates

[retailmessiah]

Hello Excel gurus,

I need a little help here, if anyone has the time. I'm attempting to
use a formula to calculate the start date, and end date of a hotel
stay. I'm trying to add this functionality to an existing sheet, and
having some trouble conceptualizing it. The sheet has columns set up
across for each day of the month. If there was a stay in the hotel on
a given day, we place a '1' in the corresponding box. Please see my
demo sheet (http://db.tt/YPC144J). I'm trying to get columns A & B to
auto-calculate if at all possible. If we had two stays in between
vacancies then I understand we can't have a start date and end date on
a single line. I've accepted that I'll have to manually split these up
onto their own lines. If these lines could error out, then I could
easily see them and split them up.

This is just a demo sheet that I put together, but hopefully it
conveys the gist of what I'm trying to do. I'm sure I haven't
explained this very clearly (as usual when I reach out to the
newsgroups for help) but please ask questions.. I would love to
discuss this with one of you experts.

Thanks for your time,
-John

 

[Claus Busch]

Hi John,

Am Thu, 7 Jul 2011 12:41:09 -0700 (PDT) schrieb retailmessiah:

This is just a demo sheet that I put together, but hopefully it
conveys the gist of what I'm trying to do. I'm sure I haven't
explained this very clearly (as usual when I reach out to the
newsgroups for help) but please ask questions.. I would love to
discuss this with one of you experts.

have a look:
http://www.claus-busch.de/excel/DailyTrackerDemo2.xls


Regards
Claus Busch

 

[retailmessiah]

Hi John,

Am Thu, 7 Jul 2011 12:41:09 -0700 (PDT) schrieb retailmessiah:


have a look:http://www.claus-busch.de/excel/DailyTrackerDemo2.xls

Regards
Claus Busch

Nice one Claus! That looks to work fairly well. I looked over the
formulae, and I don't *quite* get it all, but it works. Thanks for
your help with this. The sheet I'm working in does have the V's
present for vacancies, and for multiple ins and outs in a month, I
need it to alert me to that. So, if we look at the row D5:AG5 that's
where there are vacancies in the middle of the month. At that point
I'd love for the sheet to generate an error on the date box so that I
can split that up into 2 lines manually. I seem to think that trying
to dynamically automate this process is not too feasible, so I'm
willing to cut/paste some data around monthly to split these stays
onto multiple lines. Unless I'm overlooking something easy, I'd rather
not try to automate these.. I tried putting the Vs back in and I'm
getting some weird results. Take a look at take 3 here: http://db.tt/O44iyGQ
It looks like on some lines it's just showing the first stay, and on
other lines it's showing the end date prior to the list of Vs. Often
we start or end a month with a particular room vacant.

Do you have time to take a look again?

Thanks for your time,
John