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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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