Re: $$Excel-Macros$$ Formula to span number of nights (from Arrival Date to Departure Date) in relevant monthly Column

[De Premor]

=IF($B2>$C2,"Error Input",IF(TEXT(D$1,"ymm")=TEXT($B2,"ymm"),IF(TEXT(D$1,"ymm")=TEXT($C2,"ymm"),$C2,D$1)-$B2+1,IF(TEXT(D$1,"ymm")=TEXT($C2,"ymm"),DAY($C2),IF(AND(TEXT(D$1,"ymm")>TEXT($B2,"ymm"),TEXT(D$1,"ymm")<TEXT($C2,"ymm")),DAY(D$1),0))))

Excel attached

Pada 23/06/2013 1:07, Ali menulis:

Hi Vabz,



Is there a way to twist the formula to account for this issue?



On Thursday, June 20, 2013 12:02:23 PM UTC+4, Vabz wrote:

    Hi

    PFA, there are other solution for this.

    HTH


    On Thu, Jun 20, 2013 at 11:53 AM, Ali <hoo...@gmail.com
    <javascript:>> wrote:

        Hi all,

        I have a worksheet that need to allocate the number of nights
        spent by a guest in the relevant Columns designed for the
        purpose. I can't seem to find the right formula to do this.

        Here is a brief of the situation and the problem that need to
        be solved: Column B has the Date of Arrival (say, 29 July
        2012) and Column C has the Date of Departure (say, 03 August
        2012). That makes a total of 5 nights spent (3 nights in July
        2012 and 2 nights in August 2012).

        Columns D onwards have headers like Jul-12, Aug-12,
        Sept-12,.....June-13. (ie; July 2012 till June 2013). I need
        to formula for cell D2 whereby when I drag the formula across
        that row, it will automatically fill cells related to Column
        Jul-12 by 3 and the one related to Aug-12 by 2.

        Thank you for helping! --
        Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you
        wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook
        page of this forum @ https://www.facebook.com/discussexcel
        <https://www.facebook.com/discussexcel>

        FORUM RULES

        1) Use concise, accurate thread titles. Poor thread titles,
        like Please Help, Urgent, Need Help, Formula Problem, Code
        Problem, and Need Advice will not get quick attention or may
        not be answered.
        2) Don't post a question in the thread of another member.
        3) Don't post questions regarding breaking or bypassing any
        security measure.
        4) Acknowledge the responses you receive, good or bad.
        5) Jobs posting is not allowed.
        6) Sharing copyrighted material and their links is not allowed.

        NOTE : Don't ever post confidential data in a workbook. Forum
        owners and members are not responsible for any loss.
        ---
        You received this message because you are subscribed to the
        Google Groups "MS EXCEL AND VBA MACROS" group.
        To unsubscribe from this group and stop receiving emails from
        it, send an email to excel-macros...@googlegroups.com
        <javascript:>.
        To post to this group, send email to
        excel-...@googlegroups.com <javascript:>.
        Visit this group at
        http://groups.google.com/group/excel-macros
        <http://groups.google.com/group/excel-macros>.
        For more options, visit
        https://groups.google.com/groups/opt_out
        <https://groups.google.com/groups/opt_out>.



--

Thank you very much for looking into my problem! Your solution works fine when the Date of Arrival and Date of Departure spans over 2 consecutive months but I get an error in cases where the Date of Arrival and the Date of Departure are in the same month (eg: Arrival on 24-01-13 and Departure on 30-01-13). Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel

FORUM RULES


2) Don't post a question in the thread of another member.

4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.


---

To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 3) Don't post questions regarding breaking or bypassing any security measure. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com.

--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.

To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.

--- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.

Ali-Hotel_Arrival Departure.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet