+ Reply to Thread
Results 1 to 16 of 16

I want to know what is the exact error on S17 Cell

  1. #1
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    I want to know what is the exact error on S17 Cell

    I want to know what is the exact error on S17 Cell formula bcz all cell the same formula was working but the last cell formula was not working why? i need to know the reason so please tell me.
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: I want to know what is the exact error on S17 Cell

    The value in O17 is not a date, it's a date stored as text.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: I want to know what is the exact error on S17 Cell

    No it was date in date format please see the screenshot. both it was in date format only

    or how to change it?
    Attached Images Attached Images
    Last edited by maddyrafi1987; 01-11-2019 at 08:20 AM.

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: I want to know what is the exact error on S17 Cell

    The date in O17 (not R17 as in your screenshot) has an apostrophe in front of it - visible only in the formula bar - which makes Excel treat it as text regardless of the number format of the cell. Remove that apostrophe and your formula should work.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  5. #5
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: I want to know what is the exact error on S17 Cell

    Sir, pls see the attachment and there is attached O17 date format attached. and please tell the solution and its step procedure
    Attached Images Attached Images

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: I want to know what is the exact error on S17 Cell

    The format of a cell doesn't affect it's actual value.

    If you were to select S17, and then step through the formula using Evaluate Formula on the Formulas tab you'll see that Excel is seeing the value in O17 as "19-07-2013"

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: I want to know what is the exact error on S17 Cell

    The solution?

    Select O17 press F2 and the ENTER.

  8. #8
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: I want to know what is the exact error on S17 Cell

    Nothing was happened

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: I want to know what is the exact error on S17 Cell

    Try retyping the date in O17.

  10. #10
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: I want to know what is the exact error on S17 Cell

    tried but not working.

  11. #11
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: I want to know what is the exact error on S17 Cell

    If removing the apostrophe doesn't work, then try this:
    Copy the cell above, paste it over O17 then change the date.

  12. #12
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: I want to know what is the exact error on S17 Cell

    copy the cell above means? what i want to copy and paste to O17?

  13. #13
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: I want to know what is the exact error on S17 Cell

    Hi M,

    Change the formula in S3 to =(R3-O3)/30 and copy down.

    Regards

    peterrc

  14. #14
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: I want to know what is the exact error on S17 Cell

    No sir, still it was showing error
    Attached Images Attached Images

  15. #15
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: I want to know what is the exact error on S17 Cell

    Quote Originally Posted by maddyrafi1987 View Post
    copy the cell above means? what i want to copy and paste to O17?
    Select cell O16.
    Copy.
    Select cell O17.
    Paste.
    Change the date in O17 to 19/07/2013.

  16. #16
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: I want to know what is the exact error on S17 Cell

    Thanks for the rep and for marking the thread as Solved.

+ 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. Replies: 5
    Last Post: 02-16-2018, 06:22 PM
  2. [SOLVED] Find exact text in exact cell but on any sheet?
    By nobodyukno in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2017, 02:59 PM
  3. Vlookup NA Error not exact match
    By mizzoumadman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-05-2017, 02:56 PM
  4. [SOLVED] Exact same formula displaying error
    By Nickolai in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-12-2015, 02:11 AM
  5. Replies: 3
    Last Post: 04-25-2007, 08:27 AM
  6. [SOLVED] Match, Exact? NA error
    By nastech in forum Excel General
    Replies: 11
    Last Post: 08-14-2006, 03:30 PM
  7. Replies: 0
    Last Post: 08-25-2005, 03:46 AM

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