DATEVALUE in Excel, handy tips?

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
177
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>
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,685
Office Version
365
Platform
Windows
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
Joined
Oct 1, 2010
Messages
177
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
Joined
Oct 20, 2009
Messages
7,734
Office Version
365
Platform
Windows
If friday is excluded why do you have a friday date in your data?
 

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
177
Hi, the data is a total data table intended for lookup
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,734
Office Version
365
Platform
Windows
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
Joined
Oct 1, 2010
Messages
177
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
Joined
Aug 23, 2010
Messages
16,308
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.
 

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
177
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.
 

Forum statistics

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

Some videos you may like

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...
Top