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.
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.
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.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
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.
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: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.
Thank you so much to both of you (Mehmetcik and JeteMc), it works. Thank you.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks