+ Reply to Thread
Results 1 to 11 of 11

leave Days

  1. #1
    Registered User
    Join Date
    06-13-2011
    Location
    Charleville, Qld, Australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    leave Days

    Hello, I have a column of vacation dates beginning at cell T5 to T25, Employee IDs in column V5 to V25, and the number of days taken by each employee in column W5 to W25. Each employee has taken more than one break during the year.

    I have rows beginning at Employee IDs (column "A", and calendar months (July to June) in columns "E" to "P".

    I want to extract the vacation leave days from Column "W" and insert those days into the rows of corresponding months.

    Any ideas?

    Regards...George

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: leave Days

    Hello George, Welcome to the forum!

    If you are looking to get the total days on each months without looking for year,

    Assume E4:P4 is the months, like Jul, Aug, Sep...

    E5, copy across.

    =SUMPRODUCT(--(TEXT($T$5:$T$25,"mmm")=E$4),$W$5:$W$25)

    Please attach a sample file, we can see your data layout.

    Also, please read the forum rule & change your title to more suitable.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Valued Forum Contributor
    Join Date
    05-19-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: leave Days

    check out this sample and see if you can use it -- you will have to change the column in formula
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-13-2011
    Location
    Charleville, Qld, Australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: leave Days

    No, it didn't work correctly as there can be more than one unique entry in each month. How do I include an example for you.

    I will look for a better title name next time.

    Regards...George

  5. #5
    Registered User
    Join Date
    06-13-2011
    Location
    Charleville, Qld, Australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: leave Days

    It's okay. I just discovered how to upload a file. here is an example:
    George
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    05-19-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: leave Days

    go advanced and click on the paper clip and upload a sample workbook minus any sensitive information

  7. #7
    Valued Forum Contributor
    Join Date
    05-19-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: leave Days

    sorry George I'm stumped
    there use to be a " call in the calvary " here but i'm unable to locate it

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: leave Days

    Hello George,

    See the attached. Does this help?
    Attached Files Attached Files

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: leave Days

    What should happen if the leave days cross into the next month, or isn't that possible? For example you have leave starting on 26th of a month but it goes on for 10 days - should that be split between 2 months?
    Audere est facere

  10. #10
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: leave Days

    Good catch daddylonglegs , I think he is looking for that.

  11. #11
    Registered User
    Join Date
    06-13-2011
    Location
    Charleville, Qld, Australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: leave Days

    Ok, thanks for trying. I was given an Addif example yesterday. I may be able to adapt that to make it work by adding a Vlookup for the corresponding month number.

    Regards...George

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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