+ Reply to Thread
Results 1 to 22 of 22

Next due date of my air ticket after my leave days

  1. #1
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Next due date of my air ticket after my leave days

    Dear Sir,

    AUTO EXCEL FORMULA NEEDED FOR NEXT DUE DATE OF MY AIR TICKET AFTER REMOVING MY LEAVE DAYS.


    THANKS AND REGARDS,
    DACKSON JOSE
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    What's an air ticket?

    Seriously though... How many days a week do you work? Which days?
    Glenn



  3. #3
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Next due date of my air ticket after my leave days

    Hi sir,

    out of b4:c10 balance all days worked.

    Thanks

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Next due date of my air ticket after my leave days

    Not clear what the logic is - please explain in more detail, and don't type in ALL CAPS, as it is deemed as shouting. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    Is the YEAR of your expected answer correct???

  6. #6
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Next due date of my air ticket after my leave days

    Hi Sir,

    Yearly I have one holiday ticket approx (1000$), It is eligible for 365 worked days. Suppose in between this duration i will took 10 days leave then my next due date will be (duration 365+ leave days 10) = 375 days, means leave days exclusive for due.

    But task is formula has to calculated all of my leave days means B4:C10 area.
    Thanks.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Next due date of my air ticket after my leave days

    Did I go invisible?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    Yes. I understand the problem now. Whether intentionally or otherwise, your choice of leave dates was VERY clever. The easy formula dosn't work.


    Mmmm.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    OK. This involves a change in layout. If you REALLY want, I can try to automate the process of geting a coherent list of leave days.

    =WORKDAY.INTL($C$12,365,"0000000",$K$2:$K$51)
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Next due date of my air ticket after my leave days

    Yes Sir,

    This was very clever leave days sir, because it has to work any type of leave days will came, for example kindly change C4 date as 03/08/2019 then next due date correct answer will be 31/03/2020. The reason was, for example we add 2 more leave days and previous due date was 28-03-2020 + 2 days is 30-3-2020, But 30-3-2020 is next leave day B8:C8 then due date has to jump next day 31-3-2020.

    Thanks.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Next due date of my air ticket after my leave days

    Yup - completely invisible. Good job Glenn was able to help.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    I do not follow this:

    This was very clever leave days sir, because it has to work any type of leave days will came, for example kindly change C4 date as 03/08/2019 then next due date correct answer will be 31/03/2020. The reason was, for example we add 2 more leave days and previous due date was 28-03-2020 + 2 days is 30-3-2020, But 30-3-2020 is next leave day B8:C8 then due date has to jump next day 31-3-2020.

    Please amend your sheet and SHOW me the problem.

  13. #13
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Next due date of my air ticket after my leave days

    hi sir

    please find attachment.
    Attached Files Attached Files

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    In J2, copied down:
    =IFERROR(INDEX($B$4:$B$10,MATCH(0,INDEX(--(COUNTIF($J$1:J1,$B$4:$B$10)=$D$4:$D$10),0),0)),"")

    In K2, copied down:
    =IFERROR(J2+COUNTIF(J$2:J2,J2)-1,"")

    I created a Named Range (Leave) using this formula:

    =Sheet1!$K$2:INDEX(Sheet1!$K:$K,MATCH(10^100,Sheet1!$K:$K))

    this adjusts the range of cells selected for calculation of the result.

    Then the formula in C18, is fully automatic:

    =WORKDAY.INTL($C$12,365,"0000000",Leave)

    The helpers can be hidden, or be on another sheet, if you wish.
    Attached Files Attached Files

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    I have just seen your attachment. Please check out your problem on my most recent iteration at Post 14. You deleted the working part of my solution, so of course it does not work!!!

  16. #16
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Next due date of my air ticket after my leave days

    Sorry sir,

    This for one sample ok, unfortunately my data was this format. what do sir....

    Please help me for a solution sir...

    Thanks.
    Attached Files Attached Files

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    Whydid you now show us this from the start?

    Not very helpful. I am away for 2 hours.

  18. #18
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Next due date of my air ticket after my leave days

    take your own time sir, i will wait for you sir...

    thanks

  19. #19
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Next due date of my air ticket after my leave days

    Hi Sir,

    Please look in to priority wise.

    Thanks.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Next due date of my air ticket after my leave days

    Administrative Note:

    We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day - it has been only a couple of hours since you posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,997

    Re: Next due date of my air ticket after my leave days

    Just who do you think you are? I said I would be away for a while. Good luck and goodbye.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Next due date of my air ticket after my leave days

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

+ 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] Workday: date when we need to close the ticket and we have Friday and Saturday off
    By follow7771 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2020, 12:18 PM
  2. Find the destination and return date from the itinerary of air-ticket
    By katehiou in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2019, 01:28 AM
  3. [SOLVED] Leave tracker - how to use formula to automate the total leave days
    By yiyi2 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-16-2018, 10:59 PM
  4. Need only leave days one by one from leave attendace full data
    By sathiyamoorthy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2014, 03:23 AM
  5. Calculate total working days and excepted leave days
    By megaiooo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-21-2013, 09:29 AM
  6. Excel 2007 : Ticket Date Count
    By 4sacobb in forum Excel General
    Replies: 1
    Last Post: 07-25-2011, 04:15 PM
  7. leave Days
    By georgeawarriner in forum Excel General
    Replies: 10
    Last Post: 06-14-2011, 12:39 AM

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