Correct negative dates or times in Excel

When Excel shows ##### because the cell has a negative date or time value, make sure that you:

• Verify dates and times are positive values when you’re using the 1900 date system.
• Use a formula to subtract dates or Add or subtract time correctly to avoid negative date or time value results.

• Change negative formula results that are formatted as dates or times to a different number format.

### in Excel

 Possible Reason No. 1

If the cell width is too short, Excel either simply cuts the visible text off, or it flows into the next cell (depending if the next cell has some content in it or not).

Some cell formats (e.g. decimals) can be shortened if a cell is not wide enough to display the entire number. However, some formats (e.g. dates and times) require the cell to be wide enough to display the entire value. If the cell is not wide enough, it will display a row of hashes.

Solution

This problem is easily solved, by altering the cell width. The easiest way to do this is to drag the bar separating the column headers (as shown in the image on the right), until the cell is wider.

Alternatively, if you double-click on this bar, the cell should automatically re-size to fit the contents.

  

Possible Reason No. 2

If Excel continues to display a string of # symbols, no matter how wide you make your column, it is likely that Excel is attempting to display the contents of the cell as a date or time, but the cell contains an invalid date or time value.

As dates and times are stored as positive numeric values within Excel, some values (for example negative values) are invalid as dates or times. Excel shows this by filling the cell with # symbols.

Solution

1. If the cell is intended to contain a date or time, check your formulas. Excel dates and times must be positive numeric values. Note that Excel can not handle negative dates or times.
2. If the cell is not intended to contain a date or time, change the formatting of the cell.
   The quickest way to change a cell's formatting is to select the cell to be formatted and then select the required cell formatting from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):
   

However! If you use this cell as a data-input to, for example, a field in a merged Word document, only the first 256 characters will be grabbed!!!

ORA-01810: format code appears twice

Error Message

ORA-01810: format code appears twice

Cause of Error

You tried to use the TO_DATE function in a query, but you used a format code twice in the date format.

Resolution

Re-write the TO_DATE function so that you only use each format code once. Examples of format codes are:

Format Code	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).

For example, if you tried to execute the following SELECT statement:

SELECT TO_DATE('2004/12/14 4:29 PM', 'YYYY/MM/DD HH:MM PM' )
FROM dual;

You would receive the following error message:

Some people mistakenly use the MM format code to represent minutes, thus using the MM format for both the months and the minutes.

You could correct this SELECT statement as follows:

SELECT TO_DATE('2004/12/14 4:29 PM', 'YYYY/MM/DD HH:MI PM' )
FROM dual;