Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I tried to create a sample app, please see attached.
In tab Proposal, you will see that I selected Jul 2011 in the calendar (I replaced all Date objects with calendar date objects. I noticed, that in your original version, you had kind of mix between check In and check out dates when creating Month and year, I think you should remove these fields or name them explicitely as check_out_Month etc.)
For July 2011, I get 1202 guests staying at one of the hotels, totalling 3066 days of visit. Please note, that I included check_in and check_out day in my calculation for visit days, where e.g. the field no. of days spent is calculated as check_out - check_in Date, resulting in zero days if the dates are the same and one day if check_out is the day after check_in (where I count these visits as 1 resp 2 days.)
In your original version, you got 3 guests, but you selected explicitely check_in on July 1st and check_out July 7th, which is not giving what you intended, I think.
So please have a look at my proposal, I can explain some more, if needed.
Regards,
Stefan
Are you after number of days between the two in which case you can either do Check_In-Check_Out or if looking for number of working days NetworkDays(Check_In,Check_Out)
Hi There, thanks for the prompt feedback. What I'm actually looking for is the actual dates between the check_in and check_out columns. Example check_in is 2011/08/01 and check_out is 2011/08/05. The result of the function should display 2011/08/02, 2011/08/03, 2011/08/04. Is this possible?
Hi,
maybe your looking for something like:
=concat(Date(ValueLoop(check_in,check_out,1)),',',)
where check_in and check_out should be numerical / dates.
I just see that you want only the dates inbetween, could be:
=concat(Date(ValueLoop(check_in+1,check_out-1,1)),',',)
but haven't tested (and tried if dates are the same or sequential).
Regards,
Stefan
Aargh, I just tried if start>end of valueloop.
Won't handle properly, better to check and take care of before calling the function.
Stefan
Hi Stefan, thanks for the feedback. But it still does not work. Any other suggestions.
Please have a look at my attached document.
Depending on your requirements, it may work as shown.
Hi Stefan, thanks for the model. It works perfectly. My next step would be to query the result from this text box. Alternatively can i make use of the formula in an expression, whereby I need to say that the check_in is between =concat(Date(ValueLoop(check_in+1,check_out-1,1)),',',). Is this possible? Sorry for bugging you. Thanks,
The valueloop functions seems to have some severe limitations for use under normal conditions.
Maybe you could describe what you want to achieve, what your business case is? There might be a better, simpler solution then.
I did not understand your last requirement, where the check_on is between the date list? Could you give an example, please?
take a resident of table and
in resident
write
( check_in-check_out) as [No. of Days Spent]