Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Would like week number 1 to be first week in Jan with a Wednesday

Posted on 2014-08-29
6
Medium Priority
?
178 Views
Last Modified: 2014-08-29
Using weeknum function to number weeks but we use the first week in Jan that has a Wednesday as week 1.  Help on this please.
0
Comment
Question by:acdecal
6 Comments
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 40293288
Use it like this:

=WEEKNUM(A1,13)
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 1000 total points
ID: 40293290
I found this on the web. It assumes that A1 is January 1st of any year.

=IF(WEEKDAY(A1)<=4,A1+4-WEEKDAY(A1),A1+11-WEEKDAY(A1))
0
 

Author Comment

by:acdecal
ID: 40293327
MartinLiss, that would work but now I need a function to produce A1,  Jan 1 of the year I select.  Say I have 2015 in a cell and I want A1 to be 1/1/2015
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Assisted Solution

by:Swapnil Nirmal
Swapnil Nirmal earned 1000 total points
ID: 40293349
Say value of B1 is 2015. The formula to be as below:
=DATE(B1,1,1)
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40293352
If "2015" is in A2 the put this formula in A1
= "1/1/" & A2
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40293901
I see this question has been answered but I'm a little confused - isn't the requirement to get a week number from a date? Martin's solution returns a date, first Wednesday of the year.

btw you can get that more easily with this formula if A1 is 1st Jan date

=A1+7-WEEKDAY(A1+3)

regards, barry
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question