How to change the timing of date changing.

[Zohaib Hassan]

hi,

In AU31 I am using formula "=WEEKNUM(K31,13)" for getting the week# which give the result 9 and my week start from Wednesday which is correct one.
I want that my week start from Wednesday @ 07:00am so the week# will show 8 .

Kindly assist .

 

[Toadstool]

Hi Zohaib Hassan,

Excel holds dates as integers since 1 Jan 1900 (in Windows) and time as a fraction of the day, e.g. 12 noon is 0.5. The time 7am is 0.29167 so in the WEEKNUM calculation just subtract that from the date.

Book1
	K																																		AS	AT	AU
30	Ops Last Line																																			Month	Weeks Number
31	2/24/2020 6:50																																			2	8
32	2/26/2020 6:15																																			2	8
Sheet1

Cell Formulas
Range	Formula
AT31:AT32	AT31	=MONTH(K31)
AU31:AU32	AU31	=WEEKNUM(K31-0.29167,13)

 

[Zohaib Hassan]

Thanks a lot , it work perfect.......