SUMIF To Calculate Number Of Days Between Dates In 2 Columns Meeting Criteria
May 7, 2013
I have 2 Columns of Holiday Dates (Column A is the "FROM" date; and Column B is the 'TO' date) and Column C is Peoples Names corresponding to the Holiday dates:
ie:
COLUMN 1 ; COLUMN 2 ; COLUMN 3
A1= 2 Jan 13 ; B1= 10 Jan 13 ; C1= Tom
A2= 4 May 13 ; B2= 10 Jun 13 ; C2= John
A3= 7 Jul 13 ; B3= 10 Jul 13 ; C3= Tom
A4= 3 Aug 13 ; B4= 25 Aug 13 ; C4= John
A5= 6 Dec 13 ; B5= 15 Dec 13 ; C5= John.
I am trying to figure out a formula (eg SUMIF) to calculate and summarize the number of Days Holiday based on a person's name (eg Tom's total Number of days Holiday days) based on the above column configuration of Dates and Names.
View 4 Replies
ADVERTISEMENT
Jul 29, 2014
I am trying to calculate the number of days between two dates where the arrival date is in b5 and the departure date is in c5 for each month. I have been using the following formula =IF($C5="","",MAX(0,MIN(L$1,$C5)-MAX(K$1,$B5))) but when the stay is a full year it is giving me 365 in a single column. I think maybe I need to add in the number of days in the month but not sure how to do it.
View 2 Replies
View Related
Jul 28, 2009
I have a start date formatted as dd/mm/yyyy in cell A5
I have an end date formatted as dd/mm/yyyy in cell E5
In cell F5 I would like the result of the number of days between E5-A5 (Excluding weekends)
In cell G5 I would like the total number of days in the range F5:F25
In cell H5 I would like the total number of working days from A5 to Today
I am using the following formula to get the result in F5 etc
=SUM(INT((WEEKDAY(A5-{2,3,4,5,6})+E5-A5)/7))
I have copied this down and in the blank cells I get #NUM!
In cell F? were there is no date entered in cell E? I get a minus number (which I can hide using conditional formatting), but the minus number obviously has an affect on the result in G5, as cell G5 gives me #NUM! instead of the result for the current numbers in row F
View 9 Replies
View Related
Mar 22, 2014
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
View 9 Replies
View Related
Dec 11, 2013
I'm looking for the formula to calculate the number of winter days between two dates.
Say, that I'm having:
- Winter season start date (say Dec 1st)
- Winter season end date (say, March 1st)
=> All the days which are between these two are considered winter days for any given year.
- Two different dates defining the beginning and the end of my PERIOD. The total length of the period may be more than one year.
HOW do I calculate total number of winter days for the PERIOD?
View 13 Replies
View Related
Jun 4, 2009
I'm new here. I've searched these forums in the past for help, but never had to post, i've always been able to find something by searching. However, this time I couldn't find anything that addressed my specific need so I figured it'd be worth a shot.
Alright, I have two sets of dates, and i'm trying to set up a formular that calculates the numbers of days from the first date range that overlap/fall within the second date range.
For example:
In A3 type "1/1/2006"
In B3 type "1/1/2007"
In C1 type "6/30/2005"
In C2 type "6/30/2006"
Now, in C3 i'd like to insert a forumla that tells me how many days in the date range of 1/1/2006-1/1/2007 fall in the date range of 6/30/2005-6/30/2006. Basically for this is should be half the year, however many days that is.
Is this possible with a formula?
View 10 Replies
View Related
Sep 28, 2011
Any example of counting the # weeks/days between two dates?
View 1 Replies
View Related
Feb 3, 2014
I have been using Excel to display, sort and present data in a professional way. I have created a pivot table with two columns below. Column C indicates the start date. Column D indicates the completion date. Blank cells show live data. I need to determine the number of days. 1) That have been - completed - a) That were < 2 Days, b) >2 but less than a week, c) > than a week (all). I have used the following formulas. a) countif(D4:D61, "<3"), b) countifs(D4:D61, ">2", D4:D61, "<=8"). Easy for completed data. My problem is, how do I determine the number of days, live data that following the same queries as above. I have figured out I can use countif to count the number of blanks by - countif(D4:D61, "(blank)") but how to put it all together and what formula should I use. I am trying to use countifs or if statements but am totally lost.
Column C Column D
---------- ----------
06/01/2014(blank)
25/11/2013(blank)
13/12/201314/01/2014
27/12/201317/01/2014
27/12/201310/01/2014
View 2 Replies
View Related
Mar 19, 2014
I have two date columns, a start date and end date. Another columns tells me the number of days between these two columns. It may return 45 days which actually straddle 3 different months.
My aim is to identify between this date range how many days in Apr 14. Then in the next column howmany days in May 14 etc
So on the spreadsheet Id have 12 columns for each month of the year. in Apr 14 for each row between the given date range I want it to tell me how many days are actually in April. Id then copy that formula into the May 14 column.
View 1 Replies
View Related
Nov 26, 2009
I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact? For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.
View 4 Replies
View Related
Jul 5, 2007
I'm trying to calculate the number of days between dates in column A and B. I've looked at the examples in this site and thought I used the formula correctly, but the cell returns an error message when I type: =DATEDIF(A1,B1,"D")
View 7 Replies
View Related
Oct 31, 2013
We want to count the numbers of days between two dates however of 2nd date is null...count till todays date
If ActivityType is "E" and DDate is null Count number of days from EDate till Today().
If ActivityType is "D", Count number of days between EDate and DDate.
Column F is the desired result, Number are asuming todays date is 10/31/2013
[Code]....
View 5 Replies
View Related
Feb 28, 2008
I'm trying to do a sumif based a range of dates but keep getting a "-" for the result.
In the past when using dates I've had to value the dates in order for it to work. I can't seem to figure out the formula this time around - any thoughts? See attached for sample - my 'broken' formula is in cell C29.
View 4 Replies
View Related
Nov 21, 2008
I need to calculate the number of days between a string of dates in Column A. There will be blank cells in between the dates and I need to ignore those blank cells. I'm trying to use this formula: =DATEDIF(A13,A15,"d") but it returns an error with a blank cell in the string.
View 4 Replies
View Related
Oct 23, 2013
My sheet looks like this - example:
ID Dates CombinedAverageDaysFromID
0001 2012-02-01 ?
0001 2013-08-10
0001 2013-10-10
0402 2011-01-02 ?
0402 2013-01-05
0402 2013-01-22
0003 2009-02-04 ?
0003 2009-12-04
0003 2010-01-04
0003 2010-03-03
0003 2010-08-02
0003 2012-04-04
0003 2013-01-05
0003 2013-10-03
I need to calculate the average days between the dates for each ID? How do I do this?
View 4 Replies
View Related
Sep 14, 2006
i have a few fields with dates in my excel sheet. I also have a field, where i want to automatically insert the business day between two other date-fields! Business date means days without the weekend and public holiday! Is it possible to do that? how?
View 9 Replies
View Related
Feb 23, 2014
Here is my set up:
A2 to BF2 is a range of dates
A3 to BF3 are sales. Days without sales are 0.00
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
View 3 Replies
View Related
Apr 29, 2006
I am looking for formula to calculate the dates between two days (excluding the weekends)
I am working in Saudi Arabia and the weekend days over here are thursday and Friday.
View 9 Replies
View Related
Feb 2, 2008
I am attempting to get the formula in Column "E" (see attached excel file) to work based off the individual reps 'days off / holidays' (Columns K:P (or more as needed) that they might have. the formula I am using (which works fine) is:
=IF(C2=D2,0, SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C2&":"&D2)),2)={2,3,4,5,6,7})*(COUNTIF($K$2:$S$2,ROW(INDIRECT(C2&":"&D2)))=0)))
My issue comes up when I am coping this formula for say 100+ rows. When I copy the formula to all of the rows, the (COUNTIF($K$2:$S$2) becomes static and does not realize that the row (ie. Row 26 Rep C) has different days off than Rep A. I can manually change the reference for the countif to specify the correct row to their own days but that becomes tedious very quickly. I was wondering if anyone has come across a way to make the countif work based off of that the name in Column B matches the name in Column G then looks 'to the right' and uses the 'days off' that are listed for the corresponding Rep. I have tried a few different ideas but nothing has worked so far.
View 2 Replies
View Related
Oct 24, 2006
I have a spreadsheet that has 35000 entries and i am trying to count how many material are assigned to a particular group but cannot get it to work. I have included a test spreadsheet to explain what i am trying to achieve
View 3 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
Feb 28, 2008
I am trying to compile a count of dates using a numerical reference which adds x consecutive dates onto the initial start date based on the number in a cell.The dates added cannot fall on a weekend.
The start date for each calculation begins in D4 with the corresponding number of days to add onto this date in K4. In the example below 8/11/2007 -- 2 means that the 8th and 9th of Nov get one added to their count. As the next row also has 9th Nov as a start date one more is added to 9th Nov. As the 10th and 11th were a weekend they are skipped and the count starts again from the next Monday. I have enclosed a simplified worksheet with some sample data.
Example:
D4 --------- K4
8/11/2007 -- 2
9/11/2007 -- 3
Expected output:
AE4---------AF4
8/11/2007 -- 1
9/11/2007 -- 2
12/11/2007 --1
13/11/2007 --1
View 5 Replies
View Related
Apr 28, 2013
Our company has been taken over by new mgt (Y co) on 1st Nov 2012 from old mgt (X co). Y co has the policy of providing employees with EOS benefits as mentioned below:
1st 5 yrs - 1 month salary per year
5-10 yrs - 1.5 month salary per year
+10 yrs - 2 months salary per year
But for deciding the slab, total period of service is considered. The below table provides the calculation of days as on 31st Dec 2012 based on different joining dates which I need to fix by way of formula.
Name
Hire date
Salary
Total service (days)
X co
Y Co
1st 5 yrs
5-10 yrs
+10 yrs
George
09-02-2005
1000
2883
2822
61
61
[Code] ........
View 4 Replies
View Related
Sep 4, 2008
I have 2 dates a Start Date and an End Date.
I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column.
Start End Yr1 Yr2 Yr3
11/01/99 11/01/00 11/01/01
10/31/00 10/31/01 10/31/02
Col A Col B Col C Col D Col E
10/31/00 07/05/01 365 117 0
I thought a series of IF statements but I am open to an easy solution...
View 9 Replies
View Related
May 24, 2007
I have a start date (say 01/04/07) and I need to add a number of days to it to get the start of the next financial period. Unfortunately, I need to ignore a small list of dates (bank holidays, etc).
View 9 Replies
View Related
Nov 10, 2008
I am trying to set up a function to derive a number of days between 2 dates.
Within the table I have an on hire date, and off hire date, and an optional suspend hire date range in 2 other columns - ie. there are 4 columns with dates in.
On hire is in column B; offhire in C; suspend hire in G and recommence hire in H. The optional suspend hire date range will always fall within the main hire date range.
Column I calculates the number of days for the suspend hire period.
My key cut off date is in cell C4.
There are 3 different scenarios I need the function to cover.
1 - if the cut-off date (in C4) is later than recommence hire, calculate the number of days from hire start to cut-off date, less suspend hire period days.
2 - if the cut-off date (in C4) falls within the suspend hire period, calculate the number of days from the hire start to the begining of the suspend hire period.
3 - if the cut-off date (in C4) falls before the suspend hire period, calculate the number of days from the hire start to the cut-off date.
This is the fuction I have written:-
=IF($C$4>H10*($C$4-B10-I10),
IF(AND($C$4>G10,$C$4
View 9 Replies
View Related
Jul 3, 2007
I couldn't find a solution to my problem in the forum: Is there are formula that will allow me to calculate number of days between two dates? The date format is eg. 07/06/07
View 3 Replies
View Related
Jul 7, 2013
How can i get the actual Payment due date from no. of days using macro. i. e.
Posting Date Dues Payment Date
------------ ------ ---------------
22.06.2013 21 days ???
View 9 Replies
View Related
May 18, 2006
I need to calculate on a spreadsheet the number of days between a specific date and the date the spread sheet is opened. I need to know the number of days a merchandise has been stored.
View 5 Replies
View Related
Mar 17, 2014
I have a list of months in column A and would like a formula in column B that calculates the number of calendar days for each month (i.e. Jan=31, Feb=28 etc.)
View 9 Replies
View Related
