# How To Calculate Date Fall In Which Week

Jul 28, 2009Need a solution for calcuate a week in user enterd date?

Example

A1 A2

07/01/2009 1

07/01/2009 5

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

Example

A1 A2

07/01/2009 1

07/01/2009 5

ADVERTISEMENT

Please see the attached xls file so see what I am referring to.

I have shipments that are going to different destinations (rotterdam, austria, london, etc.)

I would like to count the arrival dates in column H that fall under each week's span, but ONLY IF its corresponding value in column F is 'rotterdam'.

Column C contains the ideal numbers that I would like column B's formula to return. I plan on doing this for the entire year, but if someone can some up with a formula, I might be able to modify it for the rest.

Note: This is only an example spreadsheet, I am going to be referencing an external file with much more information on it.

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?

View 2 Replies View RelatedI 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.

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)

Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.

If:

A1 contains beginning date of employment

B1 contains ending date of employment

C1 contains specified beginning date (criteria)

D1 contains specified ending date (criteria)

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"......................................

I have in column A the units, and in B and C the date changes. I want, when I pick a month from the box, that it only picks the IN and OUT date that applies to the month that I picked (per unit). For ex: if I choose Month march, it should show only for unit 19902506 for example: A2 and A5. And not A7 and A11. As March falls in between the Start and End Date. Is that passible? Something with OFFSET? I managed to find the latest known date with the formula =lookup (Column M:N) but Im not sure

View 2 Replies View RelatedI have a large database that is updated daily. From within the database I need to:

1. Select all records where date field A is 14 days or less than date field B

AND

2. Where a field C matches a text criteria, i.e., =DOGGIE

AND

3. Append KITTY to field D of all records that match criteria 1 and 2

Finally

4. SAVE results as a text file.

I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10

10/15/2009 01/13/2009

I'm using Excel 2007.

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.

I have a series of data in different columns, one of them is the date (daily, for like 5 years, exept weekends and national holidays), but I want to make another table with weekly data (which should be the sum of the daily data in a week, for each column). I thought there might exist any combination of formulas for that, to put once and drag it, and maybe it's pretty obvious, but even though I thought about it a lot, I couldn't find the solution.

View 6 Replies View Relatedway to calculate the hours every one has done each week to make sure everyone has done the correct hours. I was about to start using a calculator and then realised it would take forever.

I've included a zip file of the excel file

I have two rows one with the nr of weeks and another with revenues like that:....

i need to calculate the cumulative sum without inserting another row in the week that i define like an input so it could be changed.. for example the *** sum in the week 5 shloud be 900.

I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.

Excel File as follows:

Planned Delivery Time Planned Order Day Planned Delivery Day

8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)

13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)

I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.

to calculate no.s of workdays between two given dates,

1.We work 06 days a week (so only Sunday is OFF)

2.What if I have some holiday between the given dates + Sunday OFF

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 ................

On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:

(value*Tvalue)+(value*Tvalue)+(value*Tvalue)+(value*Tvalue)/(Tvalue)

(this is not the actual formula but simplified, that's not really important).

It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):

I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula

(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be:

(0.2*6)+(0.3*6)+(0.6*6)+(0.9*6)/(6).

Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective

I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.

Please refer to attached sheet.

I am using the attached to schedule the employees. All I want is a formula to add up all schedule hours per week per schedule in cell R2,R3 and R4.

Book2.xlsx

calculate overtime hours from daily time entries.

Normal hours are 7.6 per day

Time 1/2 is hours over 7.6 but no more than 2 hours

Double Time is all hours over that.

I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.

WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068

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.

I need to calculate the absolute week and have found a formula to do so. Is there a way to embed/execute this in VBA?

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date which you are trying to find the corresponding week number (for example Now().

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

View 1 Replies View RelatedI 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)

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

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

View 10 Replies View RelatedIn one column I'll have a list incrementing in 1w,2w,3w,1month and I want to be able to count the number of days that have elapsed till the latest cell. Right now I'm just winging it by saying there's always 31 days in one month using a COUNTA function, but I need it to be accurate.

View 9 Replies View RelatedI have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas

but i wanted to automate the work week calculation specially if the number of days in a month has been change.

Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.

For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.

here is the distribution of columns per week as reference.

(31 days)

wk1 - Day1 to Day8 (8 colums)

wk2 - Day9 to Day16 (8 colums)

wk3 - Day17 to Day24 (8)

wk4 - Day25 to day 31 (7) columns

(30 days)

wk1 - Day1 to Day8 (8 colums)

wk2 - Day9 to Day16 (8 colums)

wk3 - Day17 to Day23 (7)

wk4 - Day23 to day 30 (7) columns

I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.

View 2 Replies View RelatedI have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.

I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.

Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.

I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.

this was the macro that was created

Code:

ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _

xlFilterValues, Criteria2:=Array(1, "3/10/2014")

Range("A59").Select

ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"

Range("A60").Select

Copyrights 2005-15 www.BigResource.com, All rights reserved