Locale issue
 

The Month() function works for Month("Oct 2006") - gives a value of 10;
but it doesn't work if you happen to be in Sweden, you will need to use
Month("Okt 2006"). How do you overcome the problem with VBA? I would
like the locale of the worksheet to be any one of the English speaking
countries.

/Why Tea

Locale issue
 

hi Why Tea

Using the month name is not possible
If you use a ISO date string it is working in all excel versions

=MONTH("2006-10-01")

More info about the ISO standard you can find here
http://www.rondebruin.nl/isodate.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Why Tea" wrote in message ps.com...
The Month() function works for Month("Oct 2006") - gives a value of 10;
but it doesn't work if you happen to be in Sweden, you will need to use
Month("Okt 2006"). How do you overcome the problem with VBA? I would
like the locale of the worksheet to be any one of the English speaking
countries.

/Why Tea

Locale issue
 

Ron de Bruin wrote:
hi Why Tea

Using the month name is not possible
If you use a ISO date string it is working in all excel versions

=MONTH("2006-10-01")

More info about the ISO standard you can find here
http://www.rondebruin.nl/isodate.htm

Thanks Ron. Very informative Website too.

I need to use month name as the worksheet is meant for a monthly
report. Writing "2006-10-01" is not really the same as "Oct 2006". This
is especially the case when you are dealing with ISO weeks, as the
months don't always begin at the 1st.

Locale issue
 

If you want to make a report from weeks or months maybe this add-in will help you
http://www.rondebruin.nl/easyfilter.htm



"Why Tea" wrote in message ps.com...

Ron de Bruin wrote:
hi Why Tea

Using the month name is not possible
If you use a ISO date string it is working in all excel versions

=MONTH("2006-10-01")

More info about the ISO standard you can find here
http://www.rondebruin.nl/isodate.htm

Thanks Ron. Very informative Website too.

I need to use month name as the worksheet is meant for a monthly
report. Writing "2006-10-01" is not really the same as "Oct 2006". This
is especially the case when you are dealing with ISO weeks, as the
months don't always begin at the 1st.

Locale issue
 

Ron de Bruin wrote:
If you want to make a report from weeks or months maybe this add-in will help you
http://www.rondebruin.nl/easyfilter.htm


Thanks Ron. You sure have done many advanced stuff with Excel. Like
many international companies, there is always a need to do timesheets
and expense reports every month. Large companies use SAP and other
specialized tools. For not so big companies, it can be done
quick-n-dirty with Excel. Perhaps you have already done something
similar. I'd be interested to have a look.

What I have done is rather simple. For the timesheet, divide the year
in ISO weeks and then group them in months (vary from 4-6 weeks). The
month is selectable from a list, then the ISO weeks are written into
each cell. As for the expense report, there is an exchange rates with
an Update button, plus the usual stuff for expenses, etc.. I have been
learning Excel for only the past two weeks, I know there are many
things I can do better.

/Why Tea

Locale issue
 

Hi

If you want to insert the isoweeks in the cells install
http://www.rondebruin.nl/datarefiner.htm

You can insert a column in your table with the isoweek function with this add-in

With EasyFilter you can filter and copy to new sheet or workbook to create the reports you want

Try this first

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Why Tea" wrote in message ps.com...

Ron de Bruin wrote:
If you want to make a report from weeks or months maybe this add-in will help you
http://www.rondebruin.nl/easyfilter.htm


Thanks Ron. You sure have done many advanced stuff with Excel. Like
many international companies, there is always a need to do timesheets
and expense reports every month. Large companies use SAP and other
specialized tools. For not so big companies, it can be done
quick-n-dirty with Excel. Perhaps you have already done something
similar. I'd be interested to have a look.

What I have done is rather simple. For the timesheet, divide the year
in ISO weeks and then group them in months (vary from 4-6 weeks). The
month is selectable from a list, then the ISO weeks are written into
each cell. As for the expense report, there is an exchange rates with
an Update button, plus the usual stuff for expenses, etc.. I have been
learning Excel for only the past two weeks, I know there are many
things I can do better.

/Why Tea

Locale issue
 

If you want to insert the isoweeks in the cells install
http://www.rondebruin.nl/datarefiner.htm
You can insert a column in your table with the isoweek function with this add-in

I have done that using some ISO week functions found on the Net. That
worked fine.


With EasyFilter you can filter and copy to new sheet or workbook to create the reports you want

Thanks. I managed to write both forms from scratch, with some functions
taken from the Net.