DATEVALUE in Excel, handy tips?

[Bilingual]

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)


[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]DATEVALUE[/TD]
[TD] Name for unique value[/TD]
[/TR]
[TR]
[TD]01-11-2018[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02-11-2018[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03-11-2018[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-11-2018[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05-11-2018[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06-11-2018[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07-11-2018[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jon von der Heyden]

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]

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.

 

[steve the fish]

If friday is excluded why do you have a friday date in your data?

 

[Bilingual]

Hi, the data is a total data table intended for lookup

 

[steve the fish]

You want to start on a saturday and end on a friday?

=WEEKNUM(A2,16)

Still not sure what exclude Fridays means.

 

[Bilingual]

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]

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.

What about?
=YEAR(A2)&"-Week "&WEEKNUM(A2,16)

M.

 

[Marcelo Branco]

Or if you want a number, not text...

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

M.

 

[Bilingual]

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.