Sunday 11 January 2009

DATE


I am asked quite often how a date, entered on a user form, can be driven into a captured custom property.
When a date is entered into a text box, DriveWorksXpress will report the date:time code used by excel. You must let DriveWorksXpress know how the date is to be formatted. To do this we can make use of the Excel TEXT function. The TEXT function needs to know 2 things: -

  1. The item to be formatted

  2. How it is to be formatted

The rule will look something like this: -

= TEXT ( InputName , "mm/dd/yy" )

Where InputName is the user form control where the user enters the date, and the element within "" is how this is to be displayed.

This will result in 01/11/09, other formats can be used. For instance: -

"mmm-ddd-yyyy" will equal Jan-11-2009

"DDD DD,MMMM YY" will equal Sunday 11, JANUARY 09

There are many combinations of formatting that can be used, best bet is to just experiment until you hit the one you want.

The only hang up with this is the date must be entered in a specific format on the user form.

However there are some functions that calculate the date automatically, so there is no need for it to be entered on the form. The NOW function is one which returns the current date that your system is set to. This function requires no arguments, so it will just be entered as =NOW(). This again returns the date/ time code, so you must use the TEXT function with it: -

= TEXT ( NOW () , "dd/mm/yy" )

No comments: