To err is human...

Well it is always going to be our fault so we have to fix it!

So if you find that you have errors within you’re excel sheet what should you do…

The errors that you may see are:

1.  ##### – this is simply, your column is not wide enough!

2.  #DIV/0! – this is where you are dividing by 0. So you can use a simple iserror Excel 03 or lower or iferror if you have Excel 07 or you can use a selection of if statements to check whether the field =0 or “”.

3.  #NAME? – this is where Excel does not recognise the name of the formula so you need to double check your spelling or try again!

4.  #NUM! – this is most likely going to be that you are trying to do a formula against a piece of text, you may have a rouge character that you have written within the field remove this and use the number formatting to set how you want to display it as.

5.  #REF! – the cell that you were calculating does not exist, you may have deleted this row/column that it existed with.

6.  #VALUE! – this could be because the formula contains text within instead of a number value, or one of the cells contains text. You can use the same rules used for #DIV/0! to solve this problem.

If you have a  problem then drop me an email at emails-logical-test@gmail.com or leave a comment!

Cheers
Logical Test

Would you like a date?

If you use dynamic dates and would like for the dates to not show excessive characters then try the below formula, just replace [start date], and [end date] with the fields that contain the start and end date…..
 
=IF(AND(DAY([Start date])=DAY([End date]),MONTH([Start date])=MONTH([End date]),YEAR([Start date])=YEAR([End date])),TEXT([Start date],"D Mmmm YYYY"),IF(AND(MONTH([Start date])=MONTH([End date]),YEAR([Start date])=YEAR([End date])),TEXT([Start date],"D")&" - "&TEXT([End date],"D Mmmm YYYY"),IF(AND(YEAR([Start date])=YEAR([End date])),TEXT([Start date],"D Mmmm")&" - "&TEXT([End date],"D Mmmm YYYY"),TEXT([Start date],"D Mmmm YYYY")&" - "&TEXT([End date],"D Mmmm YYYY"))))

Examples and what will happen…

1.  5 March 2010 – 5 March 2010
•  Will equal…. “5 March 2010”
2.  5 March 2010 – 6 March 2010
• Will equal…. “5 – 6 March 2010”
3.  5 March 2010 – 6 April 2010
•  Will equal… “5 March – 6 April 2010”
4.  5 March 2010 – 6 April 2011
•  Will equal… “5 March 2010 – 6 April 2011” (What were you expecting the Easter bunny!! :-) )

If you know a better way why not let me know and we can create useful code for all.