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

1. ## 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. ## 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. ## 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. ## Re: Formula works on one sheet, but not for other sheet.

I think its time to upload the file...

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

Okay here's the file.

6. ## 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...

7. ## 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?

8. ## 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. ## 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)

##### Users Browsing this Thread

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

#### 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