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 RepliesI 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.
I need to calculate the date difference between two dates and get the result in the number of years and proportion of months ie;
20/09/99 to 01/02/02 is 2.33333333 years. I can use the DATEDIF function to get 2 years 4 months as the result but for the calc I'm doing I need it in the format of 2.33333333. Thinking I just need to tweak the DATEDIF a bit but just can't work it out!
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 RelatedI 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
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.
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?
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?
Any example of counting the # weeks/days between two dates?
View 1 Replies View RelatedI 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
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 RelatedI 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.
I have a DATEDIF column which I have used to calculate the age of a group of people from their dates of birth. This has produced results in cells which look like this: Age is 44 years, 1 Months and 28 Days
Where I am stuck is that I need to calculate the average of this column so I get the average of the total group. Is this possible?
I am trying to use the following formula to count the number of times a day appears in a column. It doesn't seem to be working the way I want it to. =COUNTIF($A13:$A200,"Sunday"). It could be the way the days of the week are gathered on the sheet, you can see the attached workbook to see how I am using it.
View 4 Replies View RelatedIn cell A1 = 15/06/2006 (dd/mm/yyyy)
What is the formula to find how many no. of days in month provided in cell A1.
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.
A1 B1 C1 D4
2 4 6 C1-Formula is: =sum(A1+B1)
Now I have to write formula for D4-cell:
i.e. =sum(C1/Total days remaining of the month)
Suppose Today is April 01, 2009, C1/should be devided by 30,
Tomorrow will be April 02, 2009, C1/should be devided by 29.
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
View 19 Replies View RelatedI have been using a formula to count the number of days between 2 dates and if the date was older than 12 months it would work fine.
The data that i was using has been changed and they have decided to change the format of the dates and dropped the year reference no all i have to work with is dates like 27-07 this represents 27th July i can format this date to work fine unless the date falls before 1 jan this year all i get is lots of numbers and it stuffs up the whole calculation.
The formula i am using to compare the dates is
=DATEDIF(N31,$D$1,"d")
I want to create a formula that works out the number of days between two dates but excludes weekends?
View 3 Replies View RelatedFormula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name "LARSheet1 to 1" ]
I'm trying to build "Leave, Attendance Management" system using Excel. I am about 40% there so far.
The basic structure of the workbook is as follows:
12x main monthly "Leave Attendance Record" worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ].
1 x worksheet which holds "data validation" list andvlook up info. ["FormulaListSheet"]
1 x worksheet acting as a database of staff details andpay numbers. ["StaffdBaseSheet"]
1 x summary page which shows staff reaming leaveallocation. ["SummaryLeaveSheet"]
What I'm having problems with at the moment is as follows:
A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).
I would like it to work like this:
When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.
I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.
Sample of sheet setup : screenshot_LARSheet1.PNG
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything.
So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.
I have a Training Matrix with "Main", "Leavers" and "Reports" sheets. (can email it but can't upload it here; it's 534kb and I can't make it smaller)
In the "Reports" sheet I need to fill "Site Procedural Training" - "Number of people trained in period".
The periods are :
25/12/11 - 31/03/12
01/04/12 - 30/06/12
01/07/12 - 29/09/12
30/09/12 - 29/12/12
The source of data will be the "Main" and "Leavers". The data are dates in these sheets; each date = 1
The formula needs to look in to "Main" and "Leavers" and if it finds a date which fits in the required period in "Reports" then return it as 1. If it finds 300 dates then returns 300.
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 RelatedMy 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?
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?
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.
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.
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.
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] ...........
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] ........