+ Reply to Thread
Results 1 to 10 of 10

emonth how to apply

  1. #1
    Registered User
    Join Date
    03-13-2011
    Location
    Tirana
    MS-Off Ver
    Excel 2013
    Posts
    42

    emonth how to apply

    hi
    i have check in and check out date for each guest
    i need a formula to calculate how many night each guest stayed in a room for each month. Thant means if i have u guest checking in 01 jul and check out 11 jul then we can easily find that he stayed 10 nights in July, but if i have u guest that checks in on 28 jul and checks our 5 august then i need to calculate how many nights he stayed in july and how many night in august. Any clue any help. Thank you

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: emonth how to apply

    What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Mon 25 Jun 2018) is actually 43276

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    So all you need to do is subtract the "leave" date from the "arrive" date

    (also, I think you meant EOMONTH, not EMONTH? And that function calculates what the date will be (would have been) x-months from now (or in the past) )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-13-2011
    Location
    Tirana
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: emonth how to apply

    excel.jpg

    can you check the attached pic
    and thanks a lot for your quick reply

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: emonth how to apply

    Here's one way to do it in one formula. In D2 filled down and across:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    E
    1
    Check in
    Check out
    total days
    Days in Jul
    Days in Aug
    2
    1-Jul
    10-Jul
    9
    9
    0
    3
    2-Jul
    12-Jul
    10
    10
    0
    4
    3-Jul
    9-Jul
    6
    6
    0
    5
    4-Jul
    15-Jul
    11
    11
    0
    6
    5-Jul
    13-Jul
    8
    8
    0
    7
    25-Jul
    3-Aug
    9
    7
    2
    8
    26-Jul
    5-Aug
    10
    6
    4
    9
    27-Jul
    7-Aug
    11
    5
    6
    10
    28-Jul
    5-Aug
    8
    4
    4
    11
    29-Jul
    9-Aug
    11
    3
    8
    Dave

  5. #5
    Registered User
    Join Date
    03-13-2011
    Location
    Tirana
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: emonth how to apply

    WELL DONE, and thank you so much, can i have the formula on E?

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: emonth how to apply

    Did you miss this part?

    Here's one way to do it in one formula. In D2 filled down and across:
    HTH
    Regards, Jeff

  7. #7
    Registered User
    Join Date
    03-13-2011
    Location
    Tirana
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: emonth how to apply

    sorry again, for days in july it is perfect, i need another column of days in august in my case E, so i missed the formula for E
    thank you

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: emonth how to apply

    shmemushi the formula does both columns.

    As Jeff reiterated above enter formula in D2 then fill down and across (column E). See what happens.

  9. #9
    Registered User
    Join Date
    03-13-2011
    Location
    Tirana
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: emonth how to apply

    sorry for my foolish understanding, i was looking for a hard way but it was so easy, didn't get it correctly
    Is there any way i can donate some money to you. u saved me plenty of time
    Thank you

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: emonth how to apply

    Thank you for the offer. That's very generous, but this is a free forum.

    Glad to help. Thank you for the feedback and rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Apply VBA Script based on Text in one column and apply a formula in another column
    By vvincent in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2016, 09:19 PM
  2. How to apply LEN in UDF?
    By itselflearn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2016, 05:41 PM
  3. How to apply VBA in every row
    By Evan. in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2016, 08:37 PM
  4. [SOLVED] Apply Macro First UnprotectSheet After Apply Macro ProtectAgain Solved by : Sixthsense
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-02-2014, 05:32 AM
  5. How do i apply...
    By banny85 in forum Excel General
    Replies: 1
    Last Post: 06-22-2011, 12:30 PM
  6. anyone know how to apply for a job
    By Cheeky Charlie in forum The Water Cooler
    Replies: 6
    Last Post: 01-30-2009, 05:37 AM
  7. Does an if then apply here?
    By loc066 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2008, 09:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1