Excel,Form and American Dates

  Bandy 23:07 06 Nov 2005

I've created a spreadsheet in Excel and the dates are formatted as for example 07-Nov-05.
This works when entered directly into the spreadsheet as 07/11/05.

I have also created a form in vba and when the date is inserted as 07/11/05 it enters the spreadsheet in the form of 11-July-05. Those Americans get everywhere

The obvious question is: What have I done wrong/omitted to do, and how can I correct or amend it.

Hope you can help.

  Cannuck 07:08 07 Nov 2005

You've done nothing wrong.
Excel only has American style date formats

  Cannuck 07:18 07 Nov 2005

Should have added that you can have the date set up as "July 11, 2005", for example, if you prefer that way.
Right click the cell or column that you want to format, click on "format cells", click "number", click "date", and scroll down column on right side. Click on desired style and click "ok".
That should change the date style.

  Bandy 08:47 07 Nov 2005

Thank you Cannuk

What I don't understand is that when I enter the date 7/11/05 directly into the speadsheet it appears as 7-Nov-05 but using the form the date appears in the same speadsheet as 11-July-05.

Surely something must be trating the two entries into the same spreadsheet column differently

  PaulB2005 08:54 07 Nov 2005

Poppycock! Just done my expense with British dates...

Select and right click the cells you wish to format to British. Select Format Cells, Date in the left hand column. Make sure Locale is United Kingdom. Click OK.

Hey presto, proper dates....

  Bandy 09:24 07 Nov 2005

Thanks again

I think either you've missed the point, or I am not understanding correctly.

When entering the dates directly into the spreadsheet column they appear as Britsh dates but when entered into that same column via the form they appear as American dates - that is with the day and month numbers transposed and no amount of formatting the cells makes them change once entered.

That means that for every item entered via that form has to be manually transposed back again.

At the moment when using the form the only solution appears to be to enter the dates in Americam format which the users find confusing

  PaulB2005 09:29 07 Nov 2005
  Bandy 11:59 07 Nov 2005

Thank you Paulb2005.

That link was very appropriate and I've learnt from it.

I'll tick the box as resolved.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Inside the iMac Pro - Apple's most powerful Mac yet

iMac Pro release date, UK price & specs

Comment nettoyer Windows et optimiser son PC gratuitement ?