+ Reply to Thread
Results 1 to 9 of 9

Formula works on one sheet, but not for other sheet.

  1. #1
    Registered User
    Join Date
    09-19-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Formula works on one sheet, but not for other sheet.

    I'm organizing a library checkout system for my job. I made a formula to determine how much of an overdue fee should be owed if it's $1 per day up to 14 days, after which a $20 re-stocking fee is assessed. The formula I created is as follows:

    =IF(OR(F3=DATE(1904,1,15),F3-$E$1>0),"None",IF(AND(F3-$E$1<0,F3-$E$1>=-14),F3-$E$1,-20))

    Where E1 is Today() and F3 is the due date. This function works perfectly on one sheet, but when I copy/paste and adjust the reference cells, the other sheet returns only date values. The date values are still technically correct (for example if the value is supposed to be -5, it shows up as -1/6/1904), but I want them to show up in a more user-friendly way.

    Any suggestions are appreciated! Thanks!

  2. #2
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: Formula works on one sheet, but not for other sheet.

    its not working even if you change the format of the result cell?

  3. #3
    Registered User
    Join Date
    09-19-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula works on one sheet, but not for other sheet.

    No, and I can't figure out why/how to get the date to convert to a numerical value. Could it have anything to do with the month of August? I've noticed on other forums that some people experience problems with date values pertaining to July and August, but that still doesn't make much sense since both sheets are around the same time-frame.

  4. #4
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: Formula works on one sheet, but not for other sheet.

    I think its time to upload the file...

  5. #5
    Registered User
    Join Date
    09-19-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula works on one sheet, but not for other sheet.

    Okay here's the file.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: Formula works on one sheet, but not for other sheet.

    Hi Keila, I have no idea why its not working at your end. Its working perfectly here. I only changed the cell format of the result...
    Attached Files Attached Files

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Formula works on one sheet, but not for other sheet.

    try changing a date on the sheet that it IS working, to august, and see if you get the same error?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    09-19-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula works on one sheet, but not for other sheet.

    Thanks! A co-worker of mine solved it for me! It was a really easy fix that I totally overlooked!

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Formula works on one sheet, but not for other sheet.

    If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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