I have a problem with the function weeknum, because the
last days in the year will be 53 and the days in the next
year will have week 1, even though they are in the same
week. I would like to do a custom function in VBA that
handles this in a correct way. Ie I would like the last
days to be 53 only when it actual is 53 weeks in a year,
otherwise I want the last days in a year to be in week 1.
I have tried to program it, but I am really struggling.
Anyone have any idea?

Thanks,

Peter

When do you consider there to be 53 weeks in a year. To get a full 53 weeks
is 371 days, so one would borrow 6 (or five in a leap year) from the
previous year. So the first day of the year would need to be on Sunday or
Satuday depending on when you consider a week to start (Monday or Sunday).
The first and last day of a 365 day year are on the same day.

Anyway, you would need to add some definitions/criteria on what defines a
week and how that translates to a full 53 week year.

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
I have a problem with the function weeknum, because the
last days in the year will be 53 and the days in the next
year will have week 1, even though they are in the same
week. I would like to do a custom function in VBA that
handles this in a correct way. Ie I would like the last
days to be 53 only when it actual is 53 weeks in a year,
otherwise I want the last days in a year to be in week 1.
I have tried to program it, but I am really struggling.
Anyone have any idea?

Thanks,

Peter

This page might be a help:
http://www.bygsoftware.com/examples/iso_dates.htm
It calculates the week number based on ISO 8601.

It includes the Excel formula and an online week-number calculator using
Microsoft's spreadsheet component.

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "The Excel Auditor" and "Byg Tools for VBA"



"Peter" wrote in message
...
I have a problem with the function weeknum, because the
last days in the year will be 53 and the days in the next
year will have week 1, even though they are in the same
week. I would like to do a custom function in VBA that
handles this in a correct way. Ie I would like the last
days to be 53 only when it actual is 53 weeks in a year,
otherwise I want the last days in a year to be in week 1.
I have tried to program it, but I am really struggling.
Anyone have any idea?

Thanks,

Peter