+ Reply to Thread
Results 1 to 14 of 14

Can't figure out MID function

  1. #1
    Registered User
    Join Date
    06-18-2020
    Location
    Sydney
    MS-Off Ver
    2016
    Posts
    8

    Question Can't figure out MID function

    I have written a MID function in order to try and have a predesignated sequence appear, but it is always out.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    When the date is changed it moves as intended just the numbers from the MID function are wrong.

    Capture.JPG

    Cheers in advance

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    Re: Can't figure out MID function

    Hi Ulysses98,
    It would be more useful if you could attach a sample file rather than a screen shot. Would make it much easier to follow your formula.

  3. #3
    Registered User
    Join Date
    06-18-2020
    Location
    Sydney
    MS-Off Ver
    2016
    Posts
    8

    Re: Can't figure out MID function

    Sorry Mate Here you go
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,213

    Re: Can't figure out MID function

    It would also be good to know the desired answer and why. there may be another way of getting there that is simpler

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,082

    Re: Can't figure out MID function

    Please Login or Register  to view this content.
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    06-18-2020
    Location
    Sydney
    MS-Off Ver
    2016
    Posts
    8

    Re: Can't figure out MID function

    Alright so the aim is to be able to change the date and everything update accordingly. So in B14-B17 it shows the order of what it was in 2010, im trying to use the mid function to be able to show the correct order for the year. In 2020 it should be from top to bottom 4 3 2 1. So 2021 should be 1 2 3 4, 2022 should be 2 1 4 3 and so on.

    This is a shift leave roster

    Sorry the Picture is misleading, B17 should be 2 3 1 4

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,490

    Re: Can't figure out MID function

    Your formula turns me to different way:
    Each day from 01/01/2010, with "predesignated sequence" 3241, means 01/01/2010 is "3", 02/01/2010 is "2" and so on, 01/01/2020 is "4", but 01/01/2021 is not "2" but "1"
    Therefore, I suggest in C14:
    Please Login or Register  to view this content.
    drag accross.
    Drag down for next scenarios.

  8. #8
    Registered User
    Join Date
    06-18-2020
    Location
    Sydney
    MS-Off Ver
    2016
    Posts
    8

    Re: Can't figure out MID function

    Yes this one works but stops after 2024 is entered into E3, it seems that leap years throw off the the formula

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,490

    Re: Can't figure out MID function

    I dont think the leap year could. I try to type 1/1/2024 in E3 and it still works for me.

  10. #10
    Registered User
    Join Date
    06-18-2020
    Location
    Sydney
    MS-Off Ver
    2016
    Posts
    8

    Re: Can't figure out MID function

    Yea so when I put in 2023 it is correct and shows up with 2 in C14 but when i input 2024 instead of showing 4 it shows 1 in C14

  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
    41,397

    Re: Can't figure out MID function

    Try this instead:

    =MID($B14,MOD(DATEDIF($B$13,C$13,"m")/12,4)+1,1)

    Your duplicate thread on this issue has baen closed. Please take a moment to familiarise yourself with our rules before posting again. Thanks.
    Attached Files Attached Files
    Last edited by AliGW; 07-06-2020 at 03:43 AM.
    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.

  12. #12
    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
    41,397

    Re: Can't figure out MID function

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

  13. #13
    Registered User
    Join Date
    06-18-2020
    Location
    Sydney
    MS-Off Ver
    2016
    Posts
    8

    Re: Can't figure out MID function

    Thanks mate that worked!

  14. #14
    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
    41,397

    Re: Can't figure out MID function

    You're welcome.

+ 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. Replies: 8
    Last Post: 01-22-2017, 08:02 PM
  2. Replies: 4
    Last Post: 02-03-2014, 08:05 AM
  3. IF function using a percentage figure.
    By Steve-B in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-03-2008, 11:55 AM
  4. NEED HELP! Cannot figure out complex function
    By tiger in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-15-2005, 04:05 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