Schedule - auto text insert if 28 days has passed

**clm11115** · 01-07-2018, 10:54 PM

Currently working on a project with 20+ employees with very strict scheduling parameters. We have a time off calendar used to track employee time off - the day they leave and check out of the hotel, the days they're on RR (rest and relaxation), and the days they return to work. But, there are variations of these three items (see document). We want the document to automatically populate the code "RR/E" (rest and relaxation eligible) 28 cells/days after a cell containing "RTW or CI/RTW"

The attachment better explains and shows what I mean.

PLEASE HELP! THANK YOU!

**FlameRetired** · 01-08-2018, 12:37 AM

clm11115 try this method of upload.

To attach a file to your post,

be sure to desensitize the data
click “Go Advanced” (next to Post Quick Reply – bottom right),
scroll down until you see “Manage Attachments”, click that,
click “Browse”.
select your file(s)
click “Upload”
click “Close window”
click “Submit Reply”

The file name will appear at the bottom of your reply.

**clm11115** · 01-16-2018, 10:35 PM

Thank you! Sorry for the delay, I just realized I got this notification. But it's showing it successfully uploaded.

**JohnTopley** · 01-17-2018, 07:46 AM

You will need VBA to do this as you cannot have both a formula and value in a cell.

To illustrate, you will need a formula as below which needs to be dragged across all columns thus deleting any entered values.

In C3

=IF(INDEX($C$2:$DEF$2,MAX(IF(ISNUMBER(SEARCH("RTW",$C3:$DEF3)),COLUMN($C$1:$DEF$1)-COLUMN($C$1)+1)))+27=C$2,"RR/E","")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**samba_ravi** · 01-17-2018, 08:12 AM

OR CAN TRY
C3=IFERROR(IF(C$2-LOOKUP(2,1/SEARCH("RTW",$C3:C3),$C$2:C$2)=27,"RR/E",""),"")
Try this and copy across

**JohnTopley** · 01-17-2018, 08:33 AM

Siva, You cannot copy across as you will remove "hard coded" (manual) entries

**samba_ravi** · 01-17-2018, 08:47 AM

Originally Posted by JohnTopley

Siva, You cannot copy across as you will remove "hard coded" (manual) entries

You are right John,
What I mean to say is, 1st He will apply the formula (At initial Stage / Starting Stage), later he will update the Schedule as he do regularly.

**JohnTopley** · 01-17-2018, 08:54 AM

Yes: now I understand your approach.

**clm11115** · 01-17-2018, 04:25 PM

Trying to make sure I understand correctly..... is that the formula that can be entered into VBA?

**JohnTopley** · 01-17-2018, 04:40 PM

What is suggested is that you put the formula (use the one Siva posted in post #5) in C3 and copy it across and down as far as required BEFORE you enter any data in your worksheet.

Any data you subsequently will overwrite the formula but it will work in those cells not overwritten.

Try it out by clearing data from columns G:R in rows 3/4 of the file you posted, enter the formula as above, then re-enter the G:R data.

Hopefully you will get the "RR/E" in the right dates.

**JohnTopley** · 01-17-2018, 05:45 PM

See attached:

I put formula in rows 5 & 6 THEN copied data from row 3 & 4 to illustrate how to address the problem.

Schedule - auto text insert if 28 days has passed

LinkBack

Thread Tools

Rate This Thread

Display

Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Re: Schedule - auto text insert if 28 days has passed

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculate number of days that have passed

[SOLVED] auto lock rows 6 days after date passed

Work Schedule that calculates hours, lunches, days off and vacation days

[SOLVED] showing the days passed after receiving

[SOLVED] Any Formula to know how many days have passed from one date to another?

Auto Populate Calendar days based on Month/Year and auto insert work based on dates/name

Counting the days worked and days remaining on a schedule automatically

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions