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:
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
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.
Sorry Mate Here you go
It would also be good to know the desired answer and why. there may be another way of getting there that is simpler
Please Login or Register to view this content.
Ben Van Johnson
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
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:
drag accross.Please Login or Register to view this content.
Drag down for next scenarios.
Quang PT
Yes this one works but stops after 2024 is entered into E3, it seems that leap years throw off the the formula
I dont think the leap year could. I try to type 1/1/2024 in E3 and it still works for me.
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
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.
Last edited by AliGW; 07-06-2020 at 03:43 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks mate that worked!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks