# How To Calculate Week Number That A Date Falls In

Aug 6, 2013

Of the 52 weeks in a year, I want to know the week number that a date falls in, e.g. date - 05/08/2013 falls in Week No. 32. What's the formula to get this answer?

## Get Which Week Number Will Falls In Which Month?

Nov 15, 2013

I have week numbers from 1 to 52, now i want to get which week number will falls in which month, is there any formula in excel

for eg. Week 01 - 05 will fall in January month (2014), likewise..

## How To Calculate The Week Number Of The Month Based On A Date

Dec 30, 2013

I would like to calculate the week number of the month based on a date.

Now my days would only include working weeks (Monday - Friday).

Supposed the date is 12/31/2012:

M
31-Dec
T
1-Jan
W
2-Jan
TH
3-Jan
F
4-Jan

Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:

M
28-Jan
T
29-Jan
W
30-Jan
TH
31-Jan
F
1-Feb

It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:

M
29-Apr
T
30-Apr
W
1-May
TH
2-May
F
3-May

It will be considered as Week 1 of May since it occupies only 2 days of the working week.

Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.

## Use Dates To Get Week It Falls On

Dec 12, 2008

I have a list of dates in col "A". In col "B" i would like it to display the week it falls on. Example 12/12/08 would fall under week 12/7/08 to 12/13/08.

## Calculate Week Number From Sheetname

Oct 18, 2006

Daily i import sheets into excel and the sheet name is uniformed to the following

20061017_BNKREC - 20061018_BNKREC - 20061019_BNKREC ..........

just for clarity purposes

[2006] = year, [10] = month, [17] = previous day, [_BNKREC] = report type

I'll be creating a graph to which shows account balance by week, by account.
The data will be coming in daily. i know i will need to create either a dynamic range or copy my data into a new sheet. My head is spinning because i need excel to somehow (either in a formula or VB) determine what WorkingWeek the sheet is in. I dont want to have to keep adjusting formulae or ranges when import a new sheet..

bare with me here as its hard to explain ................

## How To Calculate Date Fall In Which Week

Jul 28, 2009

Need a solution for calcuate a week in user enterd date?

Example

A1 A2
07/01/2009 1
07/01/2009 5

## Calculate Week Number And Call Correct Sub

Jul 19, 2014

I have two processes that has to run depending of the week number of the year. So even weeks has to run "sub 2" and odd weeks has to run "sub 1".

Weeks with number 1,3,5,7,9,11,13,15 etc has to call "sub 1" and weeks 2,4,6,8,10,12,14,16 etc has to call "sub 2".

The filenames consist of "name_name_YYYYMMDD.xls" (example: SteveSmith_Runningshoes_20140719.xls)

So vba needs to calculate the week number by reading the YYYYMMDD of the filename and then call the correct sub.

## Calculate Number Of Week And Days Between Two Dates?

Sep 28, 2011

Any example of counting the # weeks/days between two dates?

## Calculate Friday Based On Week Number

Jan 9, 2012

I have a drop down box that chooses the week number of the year (This is based off of a series of data from another sheet).

I need some kind of formula that calculates the following Friday based on a week number. Say for this year (2012) The following Friday for "week 1" is 1/13/12.

(This is for payroll information and I'm trying to calculate the pay date based of of data from that week)

## Calculate Total Number Of Values For Each Day Of Week?

Dec 2, 2013

My spreadsheet is set up so that Column A has dates and Column B has a value. How can I calculate the total number of values for each day of the week? I've tried a few formulas but they either didn't work or didn't actually take the value into consideration and just counted all the 'Mondays'. I'm not sure if that's clear enough, but if we're just looking at Mondays to simplify it:

Monday, 1 January 2000: 2
Monday, 8 January 2000: 5
Monday, 15 January 2000: 0

Mondays: 7

## How To Use Vlookup To Calculate The Number Of Shift Per Week For Each Agent

Dec 19, 2013

I would like to use vlookup to calculate the number of shift per week for each agent

## Formatting Date: Add The Month And The Text "Week" Before The Week Number

Nov 24, 2009

I have a column where I am convering the Date into a Fiscal week number.

For example 10/6/2009 is Work week 41
Now I want to show October Week 41

I need to add the month and the text "Week" before the week number. what is the formula I use.

## Calculate Time Elapsed And Compute Day Of Week Based On Date

May 27, 2014

1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)

2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F

2=S-S

3. How to write an If statement that assign a value to time based off this chart:
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)

2=PM (15-2259)

3= MN (23-659)

## Show Week Number Of Today Date From Starting Date

Jun 27, 2014

is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.

## Week Number For Date Relative To Date Range

Mar 17, 2008

Is there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year. eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.

## Week Number Of Date

Sep 5, 2006

I'm using excel 2003 and I searching for a small code to automaticly generate the begin- and end- date of a week (from monday till sunday) the only variable that I wanna give is the Weeknumber. So if I write a weeknumber in cell(a,1). I want the begin-date (monday) in cell (b,1) and I want the end-date (sunday) in cell (c,1).

for example:.....

## Get Week Number From Date

Feb 15, 2008

Is there a function in Excel we can use identify a certain date to be the week no. ?

For example, 15 Feb 08 is (part of) Week 7 of 2008

## Date To Week Number & Year

Oct 9, 2007

I've done a search on here to find out how to convert a date to a week number & found this: - =WEEKNUM(A1) which works fine, But I also want the result to display the year.

So 08/10/07 becomes WK40-07

I can't see how to do it!

## Format Date Cell With Week Number

Jan 8, 2009

just wondering if its possible to format a cell to display date and week number and if so how to go about it

eg

04/01/09 week 1

## Convert Fiscal Date To Corresponding Week Number

Nov 12, 2009

I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on. The fiscal year always starts on February 1 regardless of the day of the week. The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.

I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday). =INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2. I need to make the formula "not care about" the day of the week.

## Vba To Find The Month And Week Number From Given Date

Apr 22, 2009

In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)

The Week ( Monday to sunday) which need to be calculated is the week no. in the given month

like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april

## Name File Based On Date & Week Number

Aug 25, 2007

on a macro i use to open, update a file and then save it in an archive.

Opening and updating the file is no problem, but i want to save under a dynamic name in a folder structure. This is a reoccurring task, and this way I update the same file each period but save a copy with the current data in an archive under a different name in the right place of a directory (archive).

My idea is to have a hidden cell in this workbook, where I can have the name calculated by simpe excel-formula, i.e. ="Filename_"&WEEK(TODAY())&"_"&YEAR(TODAY())&".xls".

The file should go automatically into an archive-directory, lets say C:data....archive2007 (2008, 2009, etc)
I want to add the last folder to my filename, so my macro knows the first part of the path and has to go look up the actual name it gets in order to be saved.

So I end up with a cell containting the filename: 2007Filename_35_2007.xls

Now I only need the macro which looks up this name, adds it to the hard programmed path:
C:data...archive
so that the file gets saved under as: C:data....archive2007Filename_35_2007.xls

this is how i start:

Workbooks.Open Filename:= _
"C:data....Filename.xls"
' here the file is updated...
Windows("Filename.xls").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:data....archive'(how do i do this?)'", _
ActiveWorkbook.Close

## Get Date From Week Number And Year ( Fiscal Calendar)

Jan 22, 2013

I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.

## Date Range (formula Show A Week Number In The 2nd Table )

May 6, 2009

I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.

Example:
1st Table
FROM TO WK
3/27/2009 4/2/200914
4/3/2009 4/9/200915
4/10/2009 4/16/200916
4/17/2009 4/23/200917
4/24/2009 4/30/200918

2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009

Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.

## Calculating Number Late Last Week From Multiple Dates / Date Sets

Jul 2, 2014

I am putting together a simple table to display current week's data vs previous weeks. The current week's data is drawn from a status chart which changes frequently. The constant change is fine for 'Current' as I only want the current data displayed.

The problem I am having is calculating the number of late jobs that existed during the previous week.

The status log has a due date which is compared to the current date to determine 'on time' status for the current week.
Due dates are reissued regularly so I can't use

=COUNTIF(RANGE,WEEKNUM(NOW()-1)) to return data about last week from my status chart.

I have available a 'Movement Log' (in the workbook but a separate worksheet) which tracks the changes in the due date field, but I'm not sure how to integrate that data to calculate the # of jobs that were running late from the last week.

My thought is that I need to perform a count of the # of late based on a comparison of 'due date' to 'date of the last day of last week' with a way to insert the "old due date" from the movement log to replace what is shown in the status log if necessary.

Movement Log.JPG

Status Chart.JPG

## Formula To Identify Week Number Based On Date Ranges And Add Values

Feb 18, 2014

I have the following data:

Column A = Date
Column B = Reservations made per day

For ex:

A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15

Then I have a look up table where based on the date ranges it assigns a week number.

WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11

I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.

## Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets

Jan 19, 2010

I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).

Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................

## Calculate The Number Of Days From A Date Entered Into Cell A1 To Today's Date

May 19, 2009

I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:

5/10/2009 to today is -9

5/22/2009 to today is 3

## Calculate Number Of Days Between Two Date Within A Current Month Including End Date

Apr 2, 2009

I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.

## If Number Falls Within Criteria - Return Alpha Number

Apr 23, 2012

I am trying to write a formula, I have 6 sets of criteria with a lower and higher range, if the number falls within the criteria I would like it to return the Alpha number,

eg, 104, will return D

MinMaxReturn030A3160B6190C91150D151240E241360F