2
1
NOTE: I have corrected this fault... but an answer ideally requires an explanation (if anybody can explain it great).
A formula determining the end or beginning of a quarter works perfectly. However, if the formula (in the formula bar) is simply clicked (to edit) and 'ENTER', the display fails (cell is hashed) in row 5.
That is... the cell that has been examined and re-entered.
The example file is available for download, and a video of the problem (links below).
+------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
¦ C ¦ D E ¦ F ¦ G ¦ H ¦ I ¦ J ¦ K ¦ L ¦
-----+------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
3 | Wednesday | Sunday | Sunday | Sunday | Sunday | Sunday | Sunday | Sunday | Sunday | Sunday |
+------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
4 | 01/03/2017 | 05/03/17 | 12/03/17 | 19/03/17 | 26/03/17 | 02/04/17 | 09/04/17 | 16/04/17 | 23/04/17 | 30/04/17 |
5 | | | | | | END | NEW | | | |
-----+------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
-----+----------------------------------------------------------------------------------------------------------------------
C4¦ =DATEVALUE("1/3/2017") {Start date: changes "END" & "NEW" - Format: Date}
¦
C3¦ =TEXT(C4,"dddd") {Start day - Format: dddd}
¦
D4¦ =
¦ IF(C3="Monday",C4+6,
¦ IF(C3="Tuesday",C4+5,
¦ IF(C3="Wednesday",C4+4...{Finds 1st Sunday & its date - Format: dd/mm/yy}
¦
E4:L4¦ =D4+7 {Finds the next Sunday & date - Format: dd/mm/yy}
¦
D3:L3¦ =D4 {Show the date as a day - always Sunday - Format: dddd}
¦
D5:L5¦ =
¦ IF(AND(D4>="31/03/2017"+0,D4<="06/04/2017"+0),"END",
¦ IF(AND(D4>="07/04/2017"+0,D4<="13/04/2017"+0),"NEW",
¦ IF(AND(D4>="30/06/2017"+0,D4<="06/07/2017"+0),"END",
¦ IF(AND(D4>="07/07/2017"+0,D4<="13/07/2017"+0),"NEW"...{Finds end of quarter - Format: text}
-----+----------------------------------------------------------------------------------------------------------------------
The Problem:
Highlight any cell in row 5... click in the formula bar... ENTER
Boom!.... the cell becomes hashed.
If the cell is back-filled... it once again works perfectly.
Why do cells in row 5 change to hashes when the formula bar is clicked and entered?
Note: Nothing is changed.
The display recovers if back-filled or undo is used.
Here is the xlsx file for download:
https://drive.google.com/file/d/0B9HHPLN8L5MxeGZTcGNBTDJjaDA/view?usp=sharing
Here is a short video showing this strange behavior:
https://youtu.be/dXsHA_vN0zU
THE SOLUTION TO THE PROBLEM!
I changed the row 5 format to General (from Text)!
I can now edit the formula without it hashing.
Can anybody explain why?
Marco-UandL
Posted 2017-03-02T02:33:16.457
Reputation: 45
What is "06/04" in your formula? Is 04 month or day? Could you please replace these slightly exotic date forms by
DATEVALUE("2017-06-04")(etc...) and update the question? (If you switch to ISO dates, you will save yourself a lot of pain. 06/04 can be MM/DD or DD/MM based on regional settings. I really recommend to avoid it in all work.) – miroxlav – 2017-03-02T12:16:09.493I think your problem results from the missing year and its interpretation. Sometimes it is actual year 2017 and sometimes it is 1900. – IQV – 2017-03-02T12:21:49.473
I've downloaded your file, but couldn't reproduce the issue, it just works with me with all "dd/mm", "dd/mm/yy" and "dd/mm/yyyy" formats. This might be related to date format, so please share what is your regional setting and default date formats. When you have an issue with a formula it always wise to reduce it to a minimum example, e.g. try to keep only one / two
IFfunctions in your formula and reproduce the error. Then you can see calculation steps, go to formulas - evaluate formula to see how excel interprets your data. Try to compare calculation of correct and corrupted formulas. – Máté Juhász – 2017-03-02T13:00:31.127@ miroxlav I used 06/04 simply because I couldn't add the year to the date (without it failing)... thanks for the suggestion to try to use DATEVALUE. – Marco-UandL – 2017-03-02T22:42:27.653
@ IQV as per miroxlav... I tried to add the year, but it failed.... the misleading issue was (and is) that the above formula works, until a year /017 or 2017 is added.... so bizarre that the year /18 works in the formula. – Marco-UandL – 2017-03-02T22:45:51.237
@ Mate... thanks for trying the formula... that's very interesting that you can add the year to the formula, without a problem... I did try different formats, but I simply stuck with the one that works... When I add /17 or 2017 to the formula, it fails... I'll update the Q to provide format data. – Marco-UandL – 2017-03-02T22:51:07.383
The Q is updated with important new information regarding row D... simply clicking in it and hitting enter, causes it to hash???? – Marco-UandL – 2017-03-02T23:50:42.520
Problem solved ... by changing rows 5 & 6 to General from Text ... but i do not understand why this works, nor why 'text' worked until the formula was re-entered.... can anyone explain this? – Marco-UandL – 2017-03-03T16:05:56.377
"can anyone explain this?" <--- Please re-word this question title + details if this still a valid question. || Otherwise you may post the solution of the original question and it'll remove this post from the SU 'unanswered' list. – p._phidot_ – 2018-08-13T12:18:12.923
@p.phidot ... okay, I'll answer the question, vis a vis it was a formatting problem. At least we have the fix :) – Marco-UandL – 2018-08-14T16:02:08.173
The link to your video appears to be expired. Without it, it's unclear what you mean by hashing. So it will be difficult for anyone to answer why. Embedded images would be permanent. – fixer1234 – 2018-08-15T08:21:59.303
@fixer1234 sorry ... I don't know why it got switched off, but it is reactivated. Thanks for taking an interest :) – Marco-UandL – 2018-08-16T15:10:48.510