WeekNumber From Time Sheet Value

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
706
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi All,

Have a somewhat strange issue … hopefully someone can assist me !!

I have a date format extracted from a clocking in machine and the only
reference I get to the date is :

Mo 03.06.2019
Di 04.06.2019
Mi 05.06.2019
Do 06.06.2019
Fr 07.06.2019

Is there a clever formula out there which will give the week number ?? .
E.g 22


Thanks ,

Russ
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

A
B
1
Mo 03.06.2019
10​
2
Di 04.06.2019
14​
3
Mi 05.06.2019
19​
4
Do 06.06.2019
23​
5
Fr 07.06.2019
27​

<tbody>
</tbody>



B1=
IF($A1<>"",WEEKNUM(TEXT(SUBSTITUTE(MID(A1,SEARCH(" ",$A1),99),".","")+0,"00-00-0000")+0,2),"") copy down
 
Upvote 0
You could use =DAYS(DATE(2019,6,3),DATE(2019,1,1))/7

Which returns 21.9
--> As new years was on Tuesday then for Mon-Fri your decimals will be .9 to .4 (So if you round to nearest number then the working weeks fit nicely in to 1 number! i.e. 22 for the week in example.

To be more dynamic:

=DAYS(DATE(YEAR(A1),MONTH(A1),DAY(A1)),DATE(2019,1,1))/7

Drag that down, assuming the dates are in column A starting at A1, change accordingly.
 
Last edited:
Upvote 0
another way with PowerQuery

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, " "), type text}}),
    Parse = Table.TransformColumns(Extract,{{"Column1", each Date.From(DateTimeZone.From(_)), type date}}),
    WeekOfTheYear = Table.TransformColumns(Parse,{{"Column1", Date.WeekOfYear, Int64.Type}})
in
    WeekOfTheYear[/SIZE]
 
Upvote 0
Hi Marziotullio ,

Thanks or your post , your formula is correct , but I think it is reading the date format as mm.dd.yyy , when

it is dd.mm.yyy. ( Monday 3rd June 2019 )

Is there a tweak to your formula ??
Many thanks ,
Russ.
 
Upvote 0
Another solution (if your date as text)
Code:
=WEEKNUM(IFERROR(VALUE(MID(" "&A1&" ";SEARCH("?.";A1);10));VALUE(MID(" "&A1&" ";SEARCH("?.";A1);8)));2)
 
Upvote 0
Hi Navic ,

Thank you , that's great , appreciate everyone's help , as ever a place to gain excel ability !!

Russ
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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