+ Reply to Thread
Results 1 to 11 of 11

Schedule - auto text insert if 28 days has passed

  1. #1
    Registered User
    Join Date
    08-30-2017
    Location
    Washington D.C.
    MS-Off Ver
    Office 365
    Posts
    4

    Schedule - auto text insert if 28 days has passed

    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!
    Attached Files Attached Files
    Last edited by clm11115; 01-16-2018 at 10:34 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,807

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

    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.
    Dave

  3. #3
    Registered User
    Join Date
    08-30-2017
    Location
    Washington D.C.
    MS-Off Ver
    Office 365
    Posts
    4

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

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

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,439

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

    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.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,921

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

    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
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,439

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

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

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,921

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

    Quote Originally Posted by JohnTopley View Post
    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.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,439

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

    Yes: now I understand your approach.

  9. #9
    Registered User
    Join Date
    08-30-2017
    Location
    Washington D.C.
    MS-Off Ver
    Office 365
    Posts
    4

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

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

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,439

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

    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.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,439

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

    See attached:

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

+ 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. [SOLVED] Calculate number of days that have passed
    By MSE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-05-2016, 12:39 PM
  2. [SOLVED] auto lock rows 6 days after date passed
    By dmmcleod in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2014, 05:25 AM
  3. Work Schedule that calculates hours, lunches, days off and vacation days
    By tameronstarr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2014, 12:06 PM
  4. [SOLVED] showing the days passed after receiving
    By Megatronixs in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-04-2014, 09:49 AM
  5. [SOLVED] Any Formula to know how many days have passed from one date to another?
    By hcastro in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-08-2013, 07:06 PM
  6. Replies: 2
    Last Post: 07-31-2013, 02:00 PM
  7. Replies: 9
    Last Post: 09-03-2007, 08:26 PM

Tags for this Thread

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