# DATEVALUE in Excel, handy tips?

#### Bilingual

##### Board Regular
Hi i need to group dates in Excel based on a unnatural selection, namely Saturday to Thursday, so i need an alternative for WEEKNR, could you please help? - i need an unique value like WEEKNR for the dates for each period, for example ("03-11-2018 - 08-11-2018" for the dates within this group)

 Date DATEVALUE Name for unique value 01-11-2018 4 02-11-2018 5 03-11-2018 6 04-11-2018 7 05-11-2018 1 06-11-2018 2 07-11-2018 3

<tbody>
</tbody>

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Hi

What version of Excel are you using?

Assuming I understand your question, both WEEKNUM and WEEKDAY functions support unnatural selections, although I cannot recall when this came in to effect.

I believe you want option 14.
=WEEKNUM(A2,14)
=WEEKDAY(A2,14)

#### Bilingual

##### Board Regular
Hi Jon, the challenge is that it is not a whole week, its only 6 days, Friday is excluded, so i cant use the Weeknum function.

Last edited:

#### steve the fish

##### Well-known Member
If friday is excluded why do you have a friday date in your data?

#### Bilingual

##### Board Regular
Hi, the data is a total data table intended for lookup

#### steve the fish

##### Well-known Member
You want to start on a saturday and end on a friday?

=WEEKNUM(A2,16)

Still not sure what exclude Fridays means.

#### Bilingual

##### Board Regular
You want to start on a saturday and end on a friday?

=WEEKNUM(A2,16)

Still not sure what exclude Fridays means.
Im afraid its not that simple

First of all, the shown date values is only an extract from the data, the data includes all dates from 01-01-2016 to yesterday, so i need an automated solution.

I need to group the data in the 6 days in and create a unique value which resembles as Weeknr for each of the 6 days periods.

#### Marcelo Branco

##### MrExcel MVP
Im afraid its not that simple

First of all, the shown date values is only an extract from the data, the data includes all dates from 01-01-2016 to yesterday, so i need an automated solution.

I need to group the data in the 6 days in and create a unique value which resembles as Weeknr for each of the 6 days periods.
=YEAR(A2)&"-Week "&WEEKNUM(A2,16)

M.

#### Marcelo Branco

##### MrExcel MVP
Or if you want a number, not text...

=YEAR(A2)*100+WEEKNUM(A2,16)

M.

#### Bilingual

##### Board Regular
Thank you all for your tries, i really appreciate it, however it is too complicated to work with weeks missing one day, so i have told the finance dep. to include the whole week.

1,078,515
Messages
5,340,861
Members
399,396
Latest member
PBE

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...