Convert List Of Dates To Count No. Of Dates By Week

Jan 27, 2006

Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.

What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc

I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.

im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.

View 13 Replies


ADVERTISEMENT

Count All Rows (dates) That Will Come Due In A Week

Mar 27, 2009

Worksheet is an action log. It has a column containing "due dates". I want to count all rows (dates) that are "less than" today (to see what work is overdue). I also want to count all rows (dates) that will come due in a week. Conditional formatting highlights them OK but I also want to maintain a set of counts. I can do all this in a macro but I don't want to use macros, just formula.

View 2 Replies View Related

How To Count Dates Based On Week

Jul 18, 2012

I have a worksheet that lasts for a year that is something look like this:

Date Name
1/1 John
2/1 Erwin
3/1 Robert
4/1 John
16/1 Erwin
17/1 Erwin
17/1 John

In my report worksheet, I need a formula to count everyone's attendance from their participation dates based on week

Name Week1 Week3
John 2 1
Erwin 1 2
Robert 1 0

View 4 Replies View Related

Count Numerical Day In A Range Of Dates - Not Day Of Week

Jul 8, 2014

I want to find the number of times the 23rd happens between a range of dates.

Let's say I invoice a customer on 5/23/12 (Column C) and I've sent an invoice every month on the 23rd. I want to count how many invoices I've sent during the past few months. I need a formula which tells me how many times the 23rd of the month happens between 5/1/12 (in column A) and 12/15/12 (in column B).

I thought it might be =COUNTIFS(A1:B1,DAY(C1)) but that formula is for counting cells in a range.

I found formulas for counting the specific day of the week (like Tuesday) and for counting the number of cells containing a number, but not this.

View 1 Replies View Related

Difficulties With Dates (convert The Column Of Dates To Say Mar 14 2009 Type Date)

May 3, 2009

look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.

View 5 Replies View Related

Excel 2007 :: Unable To Convert Text Dates To System Dates

Oct 21, 2011

I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.

Method 1 : Using Text to Column Wizard (Excel 2007)

I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.

Then I press Control ~ on this data as shown in second view.

Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011

control ~ (overview)

Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786

2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.

Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261

2nd view - system dates are getting converted into 1911

Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911

Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).

View 4 Replies View Related

Convert Numbers, Representing Dates To Actual Dates

Oct 15, 2009

I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.

View 2 Replies View Related

Convert Imported Text Dates To True Dates

May 18, 2007

I have a very large spreadsheet that comes out of SAP and it brings out the majority of cell entries as text ie putting a ' at the begining of any number. the problem that i have is with the cells that have a date in them ie '19/04/07. I have tried varioous methods to remove this and turn the cells into date format but to no avail.

Also i do not get the option to convert text to number

View 6 Replies View Related

Convert Numbers Representing Dates To True Dates

Jun 29, 2008

I import data from my works server and dates are represented in text. example text format is yyyymmdd/19790310. What I'm trying to do is add and subtract from this date. I was trying to utilize formula and havent got close. I have a couple of parameters:

In column L I would like to represent the following

if column "B" is "A" add 38 years to column "J" dates are represented as TEXT
if column "B" is "B" add 35 years to column "J" "
if column "B" is "C" add 30 years to column "J" "
if column "B" is "d" add 30 years to column "J" "

Column "M" I would like to find the difference in column "L" and "C" I know days will be difficult it can be represented in years and months only.

I have no preference on whether I use VBA or a formula but have failed on on formula and was attempting to use VBA Case method but no success. I have 10000 rows of information and am open to any ideas attached an example of my data.

View 7 Replies View Related

Converting Dates- Dates YYYYmmdd To Convert Them To Mm/dd/yy

Feb 19, 2008

I have a long column of dates YYYYmmdd and I need to be able to convert them to mm/dd/yy so that I can see (for example) 09/18/72 instead of 19720918

View 9 Replies View Related

Count Working Days Between 2 Dates, But Return A Zero If The 2 Dates Are The Same Day

Jan 15, 2009

Today I am having a very annoying problem that really has me stumped I need to work out the lag between a Due Date and Delivered Date

But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero

Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!

View 7 Replies View Related

Counting Dates In A Range :: Count Weekend Dates

Feb 4, 2010

In Cells B2:B100, i have dates that which have been entered using a combo box (the dates type is for e.g. 14th March 2010 format)

I want a formula that will count the cells that have dates between 1st April 2010 to 30th June 2010 in cells B2:B100

Also, I would like a formula that counts weekend dates between 1st April 2010 to 30th June 2010?

View 9 Replies View Related

Auto Fill Dates: The Sheet To List The Dates In A Column For Each Month Automatically

Jun 17, 2007

I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date jan07 and the period 10 months. The rows A1 to A10 should have jan07jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.

View 6 Replies View Related

Conditional Format Dates In A Calender When Matches Dates In A List

May 14, 2009

Hi Guys, This has been bugging me for a bit now and I just can't sus it...

I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.

I have beside that a column for holidays, etc and then a another column for other events.

When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).

The formula I have been playing with (no success) is:

=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.

The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...

Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...

I have attached the spread sheet that I am working on.

View 2 Replies View Related

Count Unique Dates In A Column That Are Between Two Dates?

Dec 18, 2012

I have a sheet named "XYZ Activity" with meeting dates in column B starting with cell B4. There are duplicates in this list, that should only be viewed as one meeting. On another sheet, each company that attends the meeting has a specific join date found in Column C (C4 is the first company start date). I am trying to figure out an equation that will count how many meetings the company could have attended. I already calculate the total meetings they have attended using either of the following equations:

=COUNT(1/FREQUENCY(IF('XYZ Activity'!$C$4:$C$4000=A3,IF('XYZ Activity'!$B$4:$B$4000<>"",'XYZ Activity'!$B$4:$B$4000)),IF('XYZ Activity'!$C$4:$C$4000=A3,IF('XYZ Activity'!$B$4:$B$4000<>"",'XYZ Activity'!$B$4:$B$4000))))

=SUM(IF(FREQUENCY(IF('XYZ Activity'!C$4:C$5000=A4,IF('XYZ Activity'!G$4:G$5000="Yes",MATCH('XYZ Activity'!B$4:B$5000,'XYZ Activity'!B$4:B$5000,0))),ROW('XYZ Activity'!B$4:B$5000)-ROW('XYZ Activity'!B$4)+1),1))

View 1 Replies View Related

Count Dates & String Dates After Given Date

May 14, 2008

The attached workbook has dates in column C, although some of these dates are just strings.

I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.

At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.

not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...

View 4 Replies View Related

Sumproduct Month And Year - Count Dates From A List

May 11, 2013

I am trying to count dates from a list using sum product (I found the formula via google) I have plugged it into my spreadsheet but it does not seem to be calculating correctly.

I have attached the spreadsheet too : sumproduct_Error.xlsx

=SUMPRODUCT(YEAR(Tbl_finish[Finish]=2008)*(MONTH(Tbl_finish[Finish]=1)))

I am expecting a count of all the dates in January 2008 with the formula above.

View 5 Replies View Related

Formula To Find Missing Dates From A List Of Dates

Jan 22, 2014

I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.

Example:

Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May

I want to list the missing dates from this list.

View 4 Replies View Related

List Matching With Dates: Using Dates As Filters

Feb 12, 2009

I want to see if the people on list one are on list two. I need to come up with a formula that gives TRUE for each person on list one who is on list two and false for each person on list one who is not on list two. Several caveats: 1) there are people on list one who aren't on list two and vice versa, 2) the people have several defining characteristics which need to be met, and 3) dates are involved.

With respect to point three, TRUE should only appear if the people on list one are on list two and if they were employed by the organization on a specific date (ex: 3/5/1996); in order for the TRUE statement to appear in the G column, the person should not only be on list two but also be employed on a specific date. The specific date should fall within the start and end employment contract.

View 6 Replies View Related

Identifying Weekend Dates In A List Of Dd/mm/yy Dates

Jan 7, 2010

Is it possible to set conditional formatting to highlight any days which are saturday and sundays?

I have long long list of dates in the 17/12/2009 format, I need to be able to quickly see which dates are weekends.

View 4 Replies View Related

Pulling Specific Dates Out Of List Of Dates?

Aug 5, 2013

I have a list of dates in this format (7/20/2013) that go through several months. I would like to make have a formula that takes all of the dates from a specific month out and make a new list of those days. For example, if I have 7/20/2013, 6/28/2013, 8/3/2013, 7/1/2013. I would like to to take only the dates from July and put them in a new list: 7/20/2013, 7/1/2013.

View 3 Replies View Related

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.

View 6 Replies View Related

Identifying Dates For This Week And Next

Oct 10, 2009

i have a spreadsheet that contains dates spread across different rows and columns. How can I identifying dates for this week and next week by colouring the cells?

example dates that fall within this week will be red, next week will be blue.

View 7 Replies View Related

VBA Filter By Last Week Dates

Oct 3, 2012

Is there a way with VBA to sort lets say column A by last weeks dates. For example, if to day is Wed Oct 3, can I filter it for Sep 24-28?

All the dates in col A are in 10/2/12 format.

View 3 Replies View Related

Display Day Of Week From Dates

Mar 15, 2008

I have a column of dates in one column and in the next column next to the dates, I want to show the day of the week that each date represents, such as;

Col A Col B
1 March 2008 Sunday or Sun

etc etc..

View 5 Replies View Related

Calculating Two Values Between Two Dates (per Week)

Jun 9, 2014

I need to make a table for an injury category per shift per week. (Falls per shift per week)

I have attached an example of the spreadsheet. I have a formula in the table now that was calculating just the injury type per week but just need to add the function to read per shift but can't seem to get it to read correctly.

View 2 Replies View Related

Getting Dates To Cover Week Numbers

Jan 10, 2014

I wanted to work backwards from a weeknumber (as defined in Excel). In other words, given a week number (i.e. 5) what is the first date and last date of that weeknumber. These are the formulas that I came up with and they work fine.

Formula: [Code] ......

Ffor 1st of the week and

Formula: [Code] .......

For the last of the week.

Attached File : Weeknumber.xlsx‎

View 5 Replies View Related

Dates Showing As 1 Week Ahead / Behind?

Aug 11, 2014

I'm using this formula to look up the date stored in one vile (shown as a number, e.g 35), and then pulling this back to the first sheet in order to convert it to a date. Problem is the date keeps showing up as a week after its supposed to be.

=IF(VLOOKUP(B76,'[MAD File.xlsx]Feuil1'!$A:$K,11,0)="","",MAX(DATE(BN76,1,1),DATE(BN76,1,1)-WEEKDAY(DATE(BN76,1,1),2)+('[MAD File.xlsx]Feuil1'!K74)*7+1))

View 3 Replies View Related

How To Assign Week Number Against Dates

Apr 15, 2012

We have started a new venture from 5th April & I need to create a template for a year and assign week no against the dates. The week1 will start from 5th April & week 52 will end on 31st March.

Also there will be a summary of the Week No(Starting Date & End Date) against each week at the top of the template. I could put the formula for the summary part but I am unable to figure out how to assign week no against the dates as given below. It is very difficult to assign the week no manually for the whole year & also it is vulnerable to error. a formula across B7:B23 which can deliver the desired result?

Sheet2  ABC1Week NoStart DateEnd Date2Week15-Apr8-Apr3Week29-Apr15-Apr4Week316-Apr22-Apr5   6DateDesired
Result 75-AprWeek1 86-AprWeek1 97-AprWeek1 108-AprWeek1 119-AprWeek2 1210-AprWeek2 1311-AprWeek2 1412-
AprWeek2 1513-AprWeek2 1614-AprWeek2 1715-AprWeek2 1816-AprWeek3 1917-AprWeek3 2018-AprWeek3 2119-

[Code] .........

View 9 Replies View Related

Recognize A Series Of Dates As A Particular Week In The Year

Nov 12, 2008

My intentions are for Excel to recognize a series of dates as a particular week in the year. For example: 12/28/2008 thru 1/3/2009 equals Week 1, 1/4/2009 thru 1/10/09 equals Week 2.

I cannot for the life of me calculate a working formula.

View 7 Replies View Related







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