+ Reply to Thread
Results 1 to 6 of 6

Automatically Fill Leave Tracker with corresponding code based on date range

  1. #1
    Registered User
    Join Date
    11-07-2019
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Automatically Fill Leave Tracker with corresponding code based on date range

    Attached is an Excel-Leave Tracker template. I have added columns for start (B) and end date (C). Is there a way to automatically add code (ex: "V") to corresponding dates on Row 5 based on dates in Columns B and C? Thank you.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,831

    Re: Automatically Fill Leave Tracker with corresponding code based on date range

    Formula for E4 =IF(AND(DATEVALUE(E$5&"/"&$D$1&"/"&$D$2)>=$B8,DATEVALUE(E5&"/"&$D$1&"/"&$D$2)<=$C8),"V","")


    Or Maybe =IF(ISNA(MATCH(DATEVALUE(F$5&"/"&$D$1&"/"&$D$2),Holidays!B:B,0)),IF(AND(DATEVALUE(F$5&"/"&$D$1&"/"&$D$2)>=$B8,DATEVALUE(F5&"/"&$D$1&"/"&$D$2)<=$C8),"V",""),"PH")
    Last edited by mehmetcik; 10-18-2020 at 06:22 AM.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    11-07-2019
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Automatically Fill Leave Tracker with corresponding code based on date range

    Good morning. Thank you for a quick reply. I changed the formula on E4 then set start date to 9/4/2020 end date 9/10/2020 but "V" didn't show up on Row 8 for September 4,5,6,7,8,9,& 10. Should it at least add "V" to cell E8? Thank you again.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,881

    Re: Automatically Fill Leave Tracker with corresponding code based on date range

    I believe that the issue is that in the USA dates are read month/day/year and the formula is written for a system that works with day/month/year.
    Try pasting the following into cell E8 and then dragging the fill handle over to cell AF8:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    11-07-2019
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Automatically Fill Leave Tracker with corresponding code based on date range

    Thank you so much to both of you (Mehmetcik and JeteMc), it works. Thank you.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,881

    Re: Automatically Fill Leave Tracker with corresponding code based on date range

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. COUNTIF & day/date formula help - Annual Leave Tracker
    By Streaky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2020, 09:38 AM
  2. Replies: 2
    Last Post: 10-22-2018, 05:41 PM
  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. [SOLVED] Automatically fill in the date of the month based on the first input.
    By Richard_Thornton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-28-2017, 11:10 AM
  5. Help build a Leave tracker and Effort Time Estimation Tracker
    By cherias in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2015, 02:10 PM
  6. Fill Range based on Start/End Date
    By Regenbui in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2013, 08:02 AM
  7. Replies: 2
    Last Post: 10-11-2012, 04:49 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