Free Microsoft Excel 2013 Quick Reference

Calculate week number in month

I am trying to calculate the week number for a date in a month, i have a formula to calculate it from the start of a month but i need the count (ie week 1) to begin on the 18/01/2010 to the 14/03/2010 if this makes sense? Can anyone help?


Post your answer or comment

comments powered by Disqus
how to get week number in month in excel ?

I want to calculate week number for a month based on a date and the weekday will start from Friday.
Suppose the date is 19/08/08 then the week will be 3
If date is 22/08/08 then week shall be 4
If date is 04/09/2008 then week shall be 1
If date is 11/09/2008 then week shall be 2

I tried to get the result by googling but not found any appropriate result.

Can some expert provided the formula ?
Thanks in advance.

Hi,

In cell A1 I have a "=today()" to show todays date in dd/mm/yy format,
in cell B1, I would like to have have a number to represent the week
number in the current month. So the 2/12/05 would show in B1 as "1"
while 30/12/05 would show as "5". Using "=weeknum(A1)" gives me the
week number against the whole year (ie - 53) for todays date. How can
get B1 to show only 1 to 5 for the weeks in the current month only?

--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=496942

Hi,

In cell A1 I have a "=today()" to show todays date in dd/mm/yy format, in cell B1, I would like to have have a number to represent the week number in the current month. So the 2/12/05 would show in B1 as "1" while 30/12/05 would show as "5". Using "=weeknum(A1)" gives me the week number against the whole year (ie - 53) for todays date. How can get B1 to show only 1 to 5 for the weeks in the current month only?

I have two dates.

One is a start date and the other is the end date.

Let’s use 7/15/2006 for the start date and 4/12/2009 for the end date.

What I need to get to (in one way or the other) would look like this:

Start End 2006 2007 2008 2009 2010
7/1/06 4/12/09 5 12 12 4 0

I know I can use DATEDIF to calculate the number of months between the dates (in this case 33). But I’ve been trying for a long time to get a formula to get the number of months in each year IF the dates span over the course of years. I’d like the formula to be good enough where if it is in just one year, it calculates that and if it spans more than one year (like the example above) it will show that data.

Can anyone assist or do they know of an existing formula?

Thanks for the help…

Hi all,

This is a follow-up from:

http://www.excelforum.com/excel-prog...statement.html

I have discovered a problem with this code but I am unable to fix it.
When a month begins on a Saturday (as in November 1, 2008) which evaluates to week 1 for the month, the code puts the cursor in the column 28 (which is week 2 for my five ranges). However, because the user is allowed to enter only workdays in columns 5, 28, 51, 74 and 97, I want the first workday (M-F) of the month to be entered in column 5 and the following weeks entered in the other columns 28, 51, 74, and 97, accordingly. Hence, there would always be five work weeks in any month.

I added the following after the iWeek formula:

If Weekday(Year(Now()), Month(Now()), 1) = 7 Then
CrntWeek = Array(0, 5, 28, 51, 74, 97)
End If
but it gave a compile error

Can someone please help me fix it.

Thank you,
Gos-C

Hi. I just registered! I'm not sure if I have this thread in the right forum, but here I go with my question.

I have a spreadsheet of data for patients. I have their first visit and their last visit (so two visit per patient). I have the DATE column and other data columns. What I want to do (and don't even know if it's possible) is to create a chart representing the changes in the patients condition from first visit to the last visit. One axis (the Y-axis) will have values of the patient results and X-axis would have [b]the number of months since their last visit.

SO, every patient is represented by a line with two end points. ALL lines will begin on 0 (zero) for the X-axis because the zero represents the first visit. And the last end point of the line would represent the patients last visit.

The chart would have multiple lines NOT connected.

Is this possible in Excell? OR would I just have to calculate the number of months since the first visit by hand?

For ex. If I had a visit in 12/01/2001 and then lthe ast one in 5/10/2006, it would be 53 months since my last visit.

Thanks for any help.

I need to get the week number in excell from a cell with a date (dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with day
nº 1, first week with at least 4 days of the new year or finally the first
full week.

How to do this in excell ? I tried with weeknum() but I can't choose the
first week

Thanks.

Hi everyone.

I am currently using the following code to obtain two digit week numbers in
excel. Is there an easier or cleaner way of doing it?

If Len(DatePart("WW", Now())) < 2 Then _
maxDate = DatePart("YYYY", Now()) & " 0" & _
DatePart("WW", Now()) _
Else maxDate = DatePart("YYYY", Now()) & " " & _
DatePart("WW", Now()) _

Thanks,

John

I need a formula to calculate the number of months it will take to pay off a
loan, given interest rate, balance and the amount of payments.

I need to establish a formula which can calculate exact number of months between two dates. The end date is fixed as Sept. 1st,2007, and the exact number of months should change according the change of the starting date.

Example, Cell A1= start date = 06/27/2007
B1= end date= 09/01/2007

I tried the following:
C1= # of months= Datedif (A1,B1,"d")/30 =2.20

But I guess its not accurate. Who can help? Thanks!

Hi All,

Currently I have a vb which was created by someone else it is to calculated
week numbers in a worksheet.
Basicaly Column A (starts at A4) has the week nummer (which have to be
calculated) and Column B (Starts at B4) the Dates the paper was received, in
column C (Starts at C4) is the date when the doc was send to us.
Now the case is so that since this year (01-Jan-06) the result is always 0
I have checked and tried but I don't understand :O( (i'm alos no specialist).

Any help would be welcome.

If nessacery I can give the current formula

Kind Regards,

Willem

I need to get the week number in excell from a cell with a date (dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with day
nº 1, first week with at least 4 days of the new year or finally the first
full week.

How to do this in excell ? I tried with weeknum() but I can't choose the
first week

Thanks.

Hi,

I want to use a formula to calculate what week number in the month it is (i.e 1 to 6) from a particular date.

I know how to calculate this on an annual basis (i.e. 1 to 52) but not within the month.

This is what I have so far...

=IF(OR(D58=1, D58>=D57), ROUNDUP(DAY(D61)/7,0),ROUNDUP(DAY(D61)/7,0)+1)

d58 is a Weeday formula looking at d61 which is the date i want to look at. d57 is the weekday number of the first day of the month in cell d61.

I hope this makes sense to you all. Please help, this is driving me insane!

Thanks

I need to calculate the number of months from today to 2008 Dec 31, and
put that in an Excel cell with equal sign, and fraction of a month is
divided by 31.

How do I do that?

PS: I am not looking for dedicated VBA script module.

Thank you.

Hi,
I used the following formula to calculate the number of months passed
between two dates. Unfortunately, I need a little more accuracy than what
this provides. This formula results in full month calculations only. Is
there a way to get a more accurate calculation. I wouldn't even mind
calculating the number of weeks and dividing by 4.28 to arrive at number of
months, but I don't know the "name" for how to count weeks. The formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier postings
(which had a different original question) to "general" newsgroup, but I was
afraid it might get missed because it's not the original question. My
apologies if this is inappropriate posting behaviour.

I need to calculate the number of months from today to 2008 Dec 31, and
put that in an Excel cell with equal sign, and fraction of a month is
divided by 31.

How do I do that?

PS: I am not looking for dedicated VBA script module.

Thank you.

Hi,

I would like to calculate the number of months in a period such as 2012 dependant on a start and end date. The attached spreadsheet has an example of this but basically i need:

Start Date = 01/08/2011
End Date = 31/05/2012
Calc should be = No. of months in 2012 = 5

I need this to work for any start and end date criteria.

As always many thanks for your help

Hello everyone,

I know that weeknum() is a function that returns the week number in a particular year, for a date. Is there a function that returns the corresponding week in a month (ie Week 1 or 2 or 3 or 4?)

Thanks!

Hi All,

I need a function to calculate a week number of the given date, month wise.

for example: 8/12/2010 is the 2nd week of august calender. so the result is Week 2 and for 8/25/2010 result is week 4.

I am trying it a lot but not getting the result.

Many thanks in advance.

Lavi

Hi,

I need to calculate the number of months a deal runs in 2012 dependant on the start and end date.
I have attached a file with a basic view of this with COLUMN C showing the results I would like the formula to show.
As always many thanks for your help

Regards

What Excel formula can I use to calculate the number of months between the start and finish date? The date may or may not roll into the next year.

Below is an example of how the data is set up in Excel. I've entered the expected value in the column titled # Months, but need a formula to calculate this instead of doing the math in my head.

Item Start Date Finish Date # Months new 24-Apr 24-Jun 2 preservatino 24-Jun 25-Jun 12 demolition 25-Jun 26-Feb 8 modulars 26-Feb 26-Jul 5 set up 26-Jul 26-Sep 2 off site serv 26-Sep 26-Dec 3 other costs 26-Dec 27-May 5 Contingencies 27-May 27-Jul 2 F&E 27-Jul 27-Sep 2 CTS 27-Sep 27-Nov 2 Market Factor 27-Nov 28-Apr 5 Land 1 28-Apr 28-Jun 2 Land 2 28-Jun 28-Aug 2 Land 3 28-Aug 28-Oct 2 Land 4 28-Oct 28-Dec 2 Land 5 28-Dec Feb-29 2 Land 6 Feb-29 29-Apr 2

I want to create a function which will provide me week number for a date. The formula weeknum() doesn't solve my problem. The excel week number is defined from january while I want week number to start from a different month. The fiscal year of the company starts from 1st week of feb.

Ex.
The company's year starts from 4th Feb 2008.
if I use the formula weeknum() for the above date, i get the value as 6. But as per my requirements this should be week number 1. I would prefer a function instead of running a macro. I want to call this function from the excel file. see sample data below

DateExcel Week NumberMy week numberMy Quarter4-Feb61115-Feb7213-Mar105112-Mar116118-Mar127115-Apr161119-May19142

In addition to this, I also want to find the quarter number. Each fiscal year has 4 quarter and one quarter is equal to 13 weeks. So quarter 1 for my data is = (4th feb + 13 weeks). Once first 13 weeks are completed, quarter 2 starts and this goes on till the last quarter (Q4) is reached.
I am weak in writing functions hence need help.

I have a Start Date and an End Date, need to calculate the number of
months in between. Conditions: Start date = 15th includes the month.
Examples: between 14-04-05 and 15-06-05: 3 months, between 16-04-05 and
13-06-05: 1 month, between 16-02-04 and 03-05-05: 14 months. Thank you.
John

--
john liem


No luck finding an answer? You could always try Google.