DATEVALUE in Excel, handy tips?

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
186
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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)


DateDATEVALUE Name for unique value
01-11-20184
02-11-20185
03-11-20186
04-11-20187
05-11-20181
06-11-20182
07-11-20183

<tbody>
</tbody>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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)
 
Upvote 0
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:
Upvote 0
You want to start on a saturday and end on a friday?

=WEEKNUM(A2,16)

Still not sure what exclude Fridays means.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top