Automating NOW() + N in Excel

Back in December I noted about skipping weekends in Excel date calculations.

Have just got round to getting this working with automation from C#.

Here's the code:


//Open the Add-in workbook
objBooks.Open(((Excel.ApplicationClass)(((Excel.Application)(objBooks.Application)))).LibraryPath + @"\Analysis\ANALYS32.XLL", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

//must install add-in for it to work
//can only install if it is not installed already
objAddIn = objApp.AddIns.Add(((Excel.ApplicationClass)(((Excel.Application)(objBooks.Application)))).LibraryPath + @"\Analysis\ANALYS32.XLL", true);
if (objAddIn.Installed)
objAddIn.Installed = false;
objAddIn.Installed = true;

//Register the Add-in
objApp.RegisterXLL("ANALYS32.XLL");

//Run the auto-open macros in the workbook
objApp.ActiveWorkbook.RunAutoMacros(Excel.XlRunAutoMacro.xlAutoOpen);

Your career is your own small business

But I have always felt that when you are struggling with money, it’s really important to not let that derail your career. Your career is your own small business, and like any small business, if you don’t reinvest profits back into your career then you can’t grow.

EDSK ... not to save dates (and/or times) as strings based on formats which can change

Every developer should know not to save dates (and/or times) as strings based on formats which can change.

Such settings will, at some point, change or vary. And when they do: at best, a date may be displayed incorrectly or inconsistently; at worst, the program will crash as it is unable to correctly convert a date.


Why is this important?

Not doing this and trying to deal with varying formats can lead to:

• regionalisation issues.
• compatibility issues.
• unnecessarily complicated programs.

When saving a date and/or time as a string you should use an ISO defined format.

By always using one of these format you will never have to deal with multiple format conversions within a system.

If dates (and or times) are saved as strings in other formats a separate piece of information must also be saved which explains the format. '01/02/2008' could be the first of February or the second of February. '02/03/02' could be an even larger number of possibilities.

Of course, you'd never consider using a two figure year value though. would you?

These formats also have a number of other benefits.

• They ensure readability and accuracy amongst all systems.
• They allow for easy sorting and comparison of dates.
• They are language independent.
• Most notations are short.
• They are of a consistent length.

It might be tempting to think that, as computers process dates and times as numbers, this is how you should store them also, when saving to a file or database. The potential downside to this is that you really also need to store the date (and time) from which to start counting also. This means that two pieces of information should be stored. In practice, it is unlikely that anyone would store two pieces of information for each date being recorded by a system. If this was recorded it would likely be in a global setting, or, more likely, in the documentation somewhere. Either way these are likely to be places which are overlooked when they are most needed.


What do you do once you know this?

Don't do it!

Save dates and times using the numeric representations defined in ISO 8601:2004. Such as:

YYYY-MM-DD
YYYY-Www-D
hh:mm:ss
YYYY-MM-DDThh:mm:ss

In doing this you can have a simple set of utility functions to convert dates to strings and numerous future headaches are saved.

You can then just worry about formatting dates when they are displayed to the user. Which should be in the user defined format, of course.


What do you think?
Is this something every developer should know? Have you say in the comments.

Regional setting changes as work arounds

Telling users to change their regional settings to work around date conversion problems feels dirty. But an awful lot of people seem to see this as a solution.

Started unit testing today

I've finally seen sense and taken it upon myself to start creating unit tests (using DUnit) for my Delphi code.
No one else seems interested in writing them here and my boss doesn't want them because it will take to long to go back and add them for existing code.

I know that they will help improve the quality and testability of the code that I write though, so I've started creating them. I'm thinking of it as part of a new year motivation to write better code and improve my overall productivity.

Hopefully everyone else will see the benefit and start join in.

No, I'm not going to go and add them in for existing code. At least not until I have reason to go and do something else with that code.