+ Reply to Thread
Results 1 to 21 of 21

conditional formattngi of cell containing specific text in the adjacent column with time

  1. #1
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    conditional formattngi of cell containing specific text in the adjacent column with time

    I have read articles of using conditional formating of specific text. But i want to format with present time in the cell , i want to conditional format with specific text in adjacent column entry. If any body give solution with enclosure, i will be very much happy.If i know the formula, i will use format painter to copy to the cells i wanted. Expecting solution.
    When i select specific text it allows only one condition that is the specific text and format. i am little confused
    Last edited by rajuj; 06-20-2013 at 09:11 PM.

  2. #2
    Registered User
    Join Date
    03-18-2013
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    I am also a little confused - you need to upload an example

  3. #3
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Quote Originally Posted by Crawfinator1 View Post
    I am also a little confused - you need to upload an example
    Sir,I think that there is no need for any attachment
    in c1, if entered specific test, say ok then i want to have the time, exact date and time in d1. Let c1 be the link or actual sheet, I want the date and time to be displayed in d1. Now i hope you understand my problem. If there is any other entry, then this d1 is to be left as it is
    Last edited by rajuj; 06-19-2013 at 02:17 AM. Reason: to bve more specific

  4. #4
    Registered User
    Join Date
    03-18-2013
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    nope - no closer - no offence intended however your english is not very good!

    Spreadsheet will make all the difference

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    in c1, if entered specific test, say ok then i want to have the time, exact date and time in d1.
    So you are saying if you enter "Ok" in c1, then d1 should show CURRENT date & time ?
    If so, where you need conditional formatting ?


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  6. #6
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    To the best of my knowledge, the condition formatting allows you to specify condition and then it takes the format you choose . suppose, if i have given loan to some person, and he has returned it, if he has returned equal or more, then i could format, the cell by choosing conditional formatting cells containing greater than or equal and format to have that in different font and fill. This i could do to a numeric data. And then copying the same to other cells by format painter. But when i tried the same to a text, say as in my case ok if found in in a range in c1 cells i want to have the date and time in the adjacent cell in the same range. Hope, you understand now.
    Attached Files Attached Files
    Last edited by rajuj; 06-19-2013 at 04:01 AM. Reason: to clarify a little more

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Instead of docx, upload an excel workbook.

    Also,
    "But when i tried the same to a text, say as in my case ok if found in in a range in c1 cells i want to have the date and time in the adjacent cell in the same range
    Okay.. if you got "ok" in c1 then what next? Do you need current date & time in d1 i.e., as this is a adjacent cell to c1 ?
    Still not clear what conditional formatting would do here as it can only format the data and cannot enter data



    Regards,
    DILIPandey
    <click on below * if this helps>

  8. #8
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Pl find enclosed file. and help me. Thanks for prompt replies
    Attached Files Attached Files

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    use below formula:-


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and format the cell as whatever date / time format fits for you.

    Still not able to figure out what / where / when you need conditional formatting

    Regards,
    DILIPandey
    <click on below * if this helps>

  10. #10
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Sir, very kind of you. If i use this formula, if i enter ok, in b11, it gives 6.24.16p.m. When i copy the ok to the next cell, it produces the current time , but it also changes the time in b11 to the current time.
    Regarading your second part of the query, I want to know, how to use conditional format in case of specific text, for which option is available in excel, which i could not understand. When you could stipulate condition for numerical data, by referencing the cell as in my example , what should i do to have a conditional formatting in case of text. When i click on specific text , in the BYcell box, the next box changes to containing , so in the next box, i could only enter ok. It does not allow any cell reference to the text reference containing cell. Or i do not know the trick to how to write to conditionally format a text. Pl let me know, why this feature on text in conditional formatting and also where i am erring in my formula. Sorry for the trouble

  11. #11
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    See ... NOW() will always produce current date/time and after this you can copy paste as value it so that it does not change further. You can even use a macro code to do this copy paste values for you.

    Now what formula / error you are getting, I am not sure. Can you show your formula? thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>

  12. #12
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Thank you sir, i wiil do that. But i want to know how to give the cell reference, in the case of text
    Attached Files Attached Files

  13. #13
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    specific text -> containing -> ok


    Regards,
    DILIPandey
    <click on below * if this helps>

  14. #14
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Hi, the problem is how to refer the cell reference as in the case of numeric, say b1 or a11 in this case. I do not understand. Specifictext containing ok, what that means which cell it refers in the adjacent column

  15. #15
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    See the attached image where you can refer a cell in conditional formatting:-
    CF.jpg

    Regards,
    DILIPandey
    <click on below * if this helps>

  16. #16
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Thank you Sir, I think i have selected the wrong one in the select format box. But why that specific test is provided there. What is the use of that .

  17. #17
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    If you do not want to write that specific text there, then you can reference that from a cell .. let's say M1

    Formula: copy to clipboard
    Please Login or Register  to view this content.




    Regards,
    DILIPandey
    <click on below * if this helps>

  18. #18
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Thanks . I understand now. How to close the thread on completion of getting the solution, sir

  19. #19
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Great

    In order to mark this thread as solved, follow below approach:-

    To mark your thread solved do the following:
    - Go to the first post
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solve
    - Click Save


    Regards,
    DILIPandey
    <click on below * if this helps>

  20. #20
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    119

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    When i tried to add reputation for prompt and wisdom reply, i was getting some message that "you must spread some reputation.....like words. What does that mean. Should i get atleast one reputation before giving you one more repuation...

  21. #21
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formattngi of cell containing specific text in the adjacent column with t

    Yes.. that means you can not give reputations to same person every time


    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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