# Selecting Between Two Date Ranges (based On Days In Month)

Jul 8, 2014
I have a list of data that displays data by day, I want to select only the data for the for the current month and then the same day time frame for previous months in the data set.

E.g.

Today's date 08/07

Full days into month 7

Formula to sum data in columns B that only looks at dates 01/07-07/7

I will the adjust that formula for a January date that only pulls data for 01/01-01/07

Then repeat for Feb-June

View 3 Replies
ADVERTISEMENT
Mar 14, 2013

I've managed to sort the first half of this issue, in that I can calculate the number of days within a given month which fall inside specified to and from date parameters..

The formula I've used is thus :

Code:

=IF(EOMONTH(A13,0)EOMONTH($I$9,0),0,IF(EOMONTH(A13,0)>$I$9,$I$9-(EOMONTH($I$9,-1)),EOMONTH(A13,0)-(EOMONTH(A13,-1))))))

This examines a date value (A13 in this case) and assess whether any days within that month are between the two date parameters specified in H9 and I9.

H9 = 01/01/2011

I9 = 15/05/2011

Jan-11

31

[Code]....

What I need to do now is specify a second set of date parameters in H10 and I10 and add those into the assessment. The dates will NOT overlap.

So, for example, if I were to set H10 as 01/07/2011 and I10 as 15/07/2011, the results would appear as below

Jan-11

31

Feb-11

28

[Code].....

View 3 Replies
View Related
Jul 4, 2008

is there a formula that will say how many days there are in a month ?

B9 contains the a refrence to the first day of the month eg 1/7/2008 I want the cell above it (B8) to return 31. If the date reference is 1/6/2008 I want (B8) to return 30.

I'm using it to pro rata by the number of days in a month.

View 9 Replies
View Related
Jul 31, 2009

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.

View 9 Replies
View Related
May 1, 2006

I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.

=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday

to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.

View 11 Replies
View Related
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.

View 9 Replies
View Related
Apr 21, 2009

I have 5 columns: Employee Name, Start Date, Probation Time, Probation Ends, Benefits Start.

I want the Benefits Start column to populate the name of the month that follows the Probation Ends date.

For example Employee A starts on March 16. Probation time is 90 days. Probation Ends is 6/14/09. The Benefits start date is 7/1/09, however I want this column to just say July.

View 12 Replies
View Related
Oct 19, 2009

I run a small holiday cottage and I want to use excel to tell me which days of the year I get most enquiries for.

Every time I get an enquiry for a certain period in the cottage, I enter the dates into excel.

I have two columns - Start of holiday and End of holiday.

What I would like to do, is give each day of the year one point if someone enquires for it.

e.g. If someone asked for 3rd january to 5 january, I would give 1 point to the 3rd and 1 point to the 4th of january (but not the 5th as that is the day they would leave!)

At the moment I find it easier to count with pen and paper than use excel for this problem.

View 9 Replies
View Related
May 27, 2012

There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.

Countif Today()+90

View 5 Replies
View Related
Apr 1, 2014

Need to calculate the number of days in December between two dates 11/20/2013 and 1/20/2014, how many days are in december? a formula that can calculate this?

View 9 Replies
View Related
Aug 31, 2007

i'm trying to find a way to distribute days per month between 2 dates. I have found a great exemple that should to de trick but there is still a problem left in it. it gives in the next year (this case '08) an +31 value and a negative value

View 4 Replies
View Related
Jul 5, 2012

Using Excel 2010.

I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried

=AVERAGEIFS(DAYS360(A:A,B:B),A:A,">5/1/2012",A:A,"5/1/2012",A:A,"

View 2 Replies
View Related
Oct 15, 2008

I am trying to automatically add the last day of the month based off of the month

for example

a1 = beginning date

a2 = end date

a1 will always be the 1st day of a given month

then based off a1, know the last day,(i.e.... october 1, then put 10-31-08 into a2, or november 1, then put 11-30-08......)

View 10 Replies
View Related
Jun 25, 2010

I am having difficulty calculating the number of total overlapping days between several date ranges (6 of them to be precise)

I am using Excel 2003 and my data looks like this:

Sent Out (A) - Received (B)

01/06/2010 13:00 - 30/06/2010 13:00

02/06/2010 13:00 - 16/06/2010 13:00

09/06/2010 13:00 - 10/06/2010 13:00

21/06/2010 13:00 - 25/06/2010 9:44

23/06/2010 13:00 - 25/06/2010 10:56

23/06/2010 13:00 - 29/06/2010 13:00

I really can't wrap my head around the idea of calculating overlap (in network days) between six date ranges and was wondering if any of you would have a solution to this problem.

View 10 Replies
View Related
Jan 23, 2012

I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. Any way to identify the number of days, per calender month, that falls in a date range.

sample data...

Start Date

End Date

Old Value

New Value

08/03/2010

18/06/2010

16758.2

16758.1

[Code] .......

I need to break down the total number of days per month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

24

30

31

18

[Code] ......

As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...

I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.

(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it).

View 8 Replies
View Related
Jan 6, 2014

I am having difficulty calculating the number of total overlapping days between several date ranges for each item in another sheet which has unique items

I am using Excel 2003 and my data looks like this:

Sheet-1 Sheet-2

Item - Sent Out (A) - Received (B) Unique Item Number of days excluding overlap days

1234 01/06/2010 - 30/06/2010 1234 -

4321 02/06/2010 - 16/06/2010 4321 -

1234 09/06/2010 - 10/06/2010

4321 21/06/2010 - 25/06/2010

1234 23/06/2010 - 25/06/2010

4321 23/06/2010 - 29/06/2010

I have used the below formula found from the earlier post but need to add a condition calculating the days for each item.

=SUM(IF(MMULT((NETWORKDAYS(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7)))),2)<6)

*(ROW(INDIRECT(MIN( INT(A2:A7))&":"&MAX(INT(B2:B7))))>=TRANSPOSE(INT(A2:A7)))

*(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7))))<=TRANSPOSE(B2:B7))+0,ROW(A2:A7)^0),1))

View 6 Replies
View Related
Aug 22, 2013

i have attached the spreadsheet

basically I am trying to total this spreadsheet up to work out a sum for the month by customer number. some customers made a payment in jan 2012 and paid again in jan 2013, some paid twice in one month.

what needs to happen is

it needs to have customer number going down and the month/year going across the top with a summary paid for that month

i have attached the spreadsheet.

View 13 Replies
View Related
Oct 30, 2013

I have a a set of date ranges. The start and stop date of the ranges are listed in seperate columns. I need to count how many of those date ranges include a specific month/year. Example data is below.

Start

Stop

Month/Year

[Code].....

View 4 Replies
View Related
Jul 31, 2013

I am trying to auto generate a calendar based on two drop down menus - Month and Year.

Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.

August PM Schedule Demo.xlsx

View 2 Replies
View Related
Jun 20, 2013

I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:

09-15 16-22 23-29

with the month manually put in above it.

then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.

Budget Picture.jpg

View 12 Replies
View Related
Apr 23, 2008

I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008

View 4 Replies
View Related
Feb 9, 2007

On sheet 1 in cell A2 I have a date (2/22/07). On sheet 2 I have 4 columns of data...column A has consecutive dates. I am looking for a code that will select the range that begins with the week beginning date of the date that was inputted on sheet 1 and the week ending date. For example, the code will select the range 2/19/07 to 2/25/07 (A20:D26).

View 6 Replies
View Related
Nov 22, 2013

I want to calculate the end date of my German courses. This is how it works:

A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.

Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.

Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.

The workday function on excel cannot do this and I do not have any programming skills to work with VBA.

Legend:

*LU = lesson units; 1 LU is 45 minutes

Holidays:

28-Nov-13

29-Nov-13

08-Dec-13

09-Dec-13

25-Dec-13

31-Dec-13

01-Jan-14

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

View 7 Replies
View Related
Oct 2, 2005

function in a spreadsheet that will list all of the days in

a given month automaticaly with the entry of the 1st of the month only.

Ex;

10/01/05 entered dated

10/02/05 auto fill

10/03/05 "

. "

. "

10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month even for shorted months such as Feb.

View 10 Replies
View Related
Sep 1, 2007

I am attempting to create a template to input specific dates. If a student submits an assignment on the due date, I have set up the spreadsheet to conditionally format whether the assignment was late or not. In addition, I have a column for when feedback is to be submitted and whether it was on time or not. My problem is this...

I cannot figure out a formula or a way to determine:

If the student submits the assignment late, when will the feedback be due? For example, if submitted one day late, the instructor has an extra day to submit feedback.Two days late is an extra two days to work on it etc..

View 4 Replies
View Related
Apr 23, 2008

I have a date in col A and a time in col B. I'm trying to use an if function so that if time in Col B is greater than 15:30, the date in col A is returned plus one day. If time in col B is less than 15:30, date in col A returned with no change.

View 4 Replies
View Related
Mar 6, 2013

I have an excel sheet where I would like a user to enter a start date and end date(say Feb 1, 2013 to Feb 28,2013). And these dates will be able to change to whatever month the user wishes to look up in a specific date range(column A has 365+ dates). This post is closely related to [URL]... but I cannot figure out how to get it to work from my case.

So working off the same worksheet, I have the beginning date in AC35 and the end date in AC36 in which the user enters. I would like the dates that fall in this entire month, including the beginning and ending dates to be referenced when looking for the specific day that contains a value I have in S35.

In other words, I have data in columns A through N. The dates are in column A and S35 is a value obtained from using Max(L185:L526) where L185:L185 is manually selected each time I want a different month. I don't want this to be a manual process of scrolling down the spreadsheet to get the next month.

View 12 Replies
View Related
Dec 13, 2012

Sum based on date ranges. I am creating one logical formula.

I have an excel sheet with two columns.

A1: Date B1: Time Spent

Date

Time spent (in Minutes)

Total

[Code].....

As per above figure I want to sum based on the same dates. I.e A2 has date 1/26/2012 for which first entry is 12 so total will say 12 in C1, for the same date second entry is 14 so 12+14=26 is there in total at C2, for the third and last entry of 1/26/2012 it's 16 so total would be 12+14+16 = 42 in C3 which is grand total for 1/26/2012. I want to fill entire C column with some logical formula so that the logic holds true for various dates.

I have tried to use the IF formula like =IF(A1=A2,SUM(B1:B2)) but it fails at third cell. I have tried several ways to do this but no luck so far ....

View 2 Replies
View Related
Jan 17, 2014

creating a cell to show: no colour unless

date minus 90 days as amber

date minus 45 days as red

View 6 Replies
View Related
Jun 17, 2014

I have the following:

A1 = Date item is due. (DD/MM/YY format)

A2 = Time the item was delivered (HH:MM:SS format)

B1 = Actual Date item was delivered.

B2 = Actual Time item was delivered.

I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.

For example:

Due Date: 17/06/14 @ 16:00:00

Arrival Date: 17/06/14 @ 16:01:00

Days Late = 1

Due Date: 17/06/14 @ 16:00:00

Arrival Date: 18/06/14 @ 15:00:00

View 10 Replies
View Related