+ Reply to Thread
Results 1 to 19 of 19

Formula if date range between 2 cells falls between dates in one raw devide number equal

  1. #1
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Formula if date range between 2 cells falls between dates in one raw devide number equal

    Hi everyone , any change you can help me with a formula , trying to get the excell to auto fill the cells when i change the date or numbers (file attached )
    Looking for a formula that will auto-fill the cell in multiple columns (same row ) if they are within the date range .
    So looking if date range from C6 and D6 are within the dates from J3 to CI3 to divide E6 in equal numbers in all the cells from same row and columns that are included in date range .
    sorry if a bit confusing but english isn't my 1st language , i did fill them in manualy (j6-N6) .
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Do you work weekends or Mon-Fri only?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Sorry about last reply on previous thread ,Mon-Fri mostly , i was doing it manualy by counting the number of dates i need to devide the number and then copy same formula in all cells that fall between date range . .
    but this takes a while when you have 30-50 raws and you need to change dates and so on . so looking for an automatic way of doing it .
    thanks

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    "Mostly" mon-fri..... So if there's a 7 day job starting on Monday ... what will be shown on the weekend cells? Mostly something... or mostly nothing?? How can I tell what's required?

  5. #5
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Sorry , Mon-Friday , , will be weekends but very rare can do them manualy , so looking for weekends to be blank , thanks

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    OK!! Will take a look.

  7. #7
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Thanks a lot

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Quote Originally Posted by Glenn Kennedy View Post
    ... Mostly something... or mostly nothing?? ...
    I like that phrasing, Glenn.

    Pete

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    95% of the way there.

    1. Column B. Stop adding text. Just add the number of days.

    2. Column C. Same. Just the start date.

    3. Column D. Don't fiddle. A formula calculates the end date:

    =IF(B5="","",WORKDAY(C5,B5)+1)


    4. J5 copied across and down:

    =IFERROR(IF(AND(WEEKDAY(J$3,2)<6,$C5<=J$3,$D5>=J$3),$E5/$B5,""),"")

    5. What YOU need to do... Somewhere out of the way, compile a list (in one column) of all non-weekend holidays for the time period covered (date only.. no text). Add it to THIS sheet and repost it in THIS thread. Send me a PM to remind me when you've done it.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Thanks a lot its 100% as i wanted .
    so simple solution when you brian works the right way . sadly i missed the excel class at school

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    No it's not...

    re-read point 5 in my last post!!

  12. #12
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Sorry , saw that works got so excited didn't even finish reading , holiday days attached in Collumn CS
    Also any way of auto-filling with colour all working days cells that are getting numbers allocated ( exmple J5:N5 , Q5:U5,X5:Y5) as looking to keep all other cels white?
    tried using conditional formating but couldn't get it right yet .
    Attached Files Attached Files

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    D5:

    =IF(B5="","",WORKDAY(C5,B5-1,$CS$2:$CS$13))

    For conditional formatting:

    =OR(WEEKDAY(J$3,2)>5,ISNUMBER(MATCH(J$3,$CS$2:$CS$13,0)))

    Again, remember that dates are set with / in Excel... not with .

    You need to revisit your holiday list. Your data tabel runs from late 21 to early 2022... but the holidays are all 2021. Excel is VERY unforgiving!!

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    big thanks for help , works alright , tested holidays days , only issue i have or i didn't do something right also forgot to mention , looking that if its a holiday day not to get any number allocated .
    As you can see in file attached , i added a holiday day 23/11/2021 , but as we aren't working on holidays looking for this number to be allocated to next working day ( entered manually on Y5)
    Attached Files Attached Files

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Oh pooh. Iforgot about that. Out for a walk. Back in an hour or so... sooner if it starts raining again.

  16. #16
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Some lovely day , heavy snow in the morning , raining and now sunny , proper Irish weather

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Yea. We had all four seasons... plus few more... in the last 10 mins before I got back home.

    =IFERROR(IF(AND(WEEKDAY(J$3,2)<6,$C5<=J$3,$D5>=J$3,ISERROR(MATCH(J$3,$CS$2:$CS$13,0))),$E5/$B5,""),"")

    see file.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    02-23-2022
    Location
    london
    MS-Off Ver
    2021
    Posts
    17

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    Thanks a lot for all the help , set it up now at 100% , even better then expected .

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula if date range between 2 cells falls between dates in one raw devide number equ

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ 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. Return a number if a date falls between range of two dates
    By zmanzander in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-03-2020, 02:46 PM
  2. Return Value if date in a range of cells falls between two dates
    By kayla980 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-26-2020, 10:38 PM
  3. [SOLVED] IF/Then Formula to add to a date depending if a number falls w/in a certain range
    By RMFM55 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-06-2019, 09:59 AM
  4. [SOLVED] Determine if a date falls between a range of two dates
    By hdabbas in forum Excel General
    Replies: 9
    Last Post: 07-12-2017, 12:01 PM
  5. Replies: 15
    Last Post: 04-08-2013, 12:40 PM
  6. [SOLVED] Value with Corresponding Date if Falls Within Range of Dates
    By ebdmbfan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-26-2012, 09:14 AM
  7. Replies: 3
    Last Post: 07-31-2012, 04:44 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