Calculate Number Of Days Between Periods
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
ADVERTISEMENT
Mar 16, 2009
I'm trying to calculate the length of a work order to develop an average and future proposal estimates.
For example:
Job received = A1
In work = B1
Job completed = C1
Total days to complete = D1
I know I can enter in D1 C1-A1 and get the correct result but I want the field to calculate A1-TODAY() until a completion date is entered. This would display the number of days the job has been in work until completed. Once completed use the C1 for calculation.
Sorta like
If(C1=">=0" then (A1-Today()) else C1-A1)
I will then use conditional formatting with block colors to indicate whether it is a completed job or in work job.
View 10 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
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
Jul 5, 2009
How can days be allocated into the periods in which they fall if they run across a number of months.
The attached sample might explain it better.
View 2 Replies
View Related
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
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
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
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
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
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
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
Dec 16, 2009
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 Related
Sep 28, 2011
Any example of counting the # weeks/days between two dates?
View 1 Replies
View Related
Oct 7, 2011
I need to calculate the number of days between 2 dates including holidays and weekends. That means I cant use NETWORKDAYS as it excludes weekend.
View 9 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
May 7, 2014
In 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.
View 2 Replies
View Related
Feb 6, 2009
I need to have a cell with a date in it then the next cell needs a formula to caluclate how many days it has been since that date???? I'm a real novice with excel.
View 9 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
Feb 21, 2014
I'm trying to create a new column which calculates the number of days from the today's date to the due date of an invoice. Also a Column that an invoice is/was overdue.
Column A - Clerk inputs date that invoice was received
Column B - Clerk inputs the date that the bill was paid
Column C - Auto populates the due date of the invoice (20 days after the invoice is received)
Column D - I want a number of days to auto populate based on today's date that will show how many days we have to pay the bill. Example: today is 2/19/2014, bill is due 2/22/2014 (Column C), column D should read 3 (I would prefer is the number is black for "we have days left to pay", red for "we're behind")
*Extra bonus for Column D, if the column goes blank after a date is entered into Column B* - but not necessary
Column E - I would like if the date the bill was paid (Column B) is greater than the date the invoice is due (Column C) to show "Overdue" in the cell.
View 9 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
Apr 1, 2009
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.
View 10 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
Oct 16, 2009
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 Related
Jan 19, 2010
I m trying to do is this. For every person in company there is a table 30 days (cells) long, and 3 shifts (cells) wide. Every shift leader fills in the data in the "calendar" so every day, for every person, he will fill in number of hours.
I have to make excel process the whole calendar 30 x 3 = 90 cells, into final results for our payroll department. So the end product of the excel table should be Person name, total work days, total sick leave days, holiday days, and so on.
specific problem i have is how to calculate number of State Holiday days. You can see my best attempt in the attached file.
if there is only one number / mark entered each day (in only one of the 3 shifts) thansumproduct works ok.
View 8 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 7, 2008
I have been working on solving this riddle since yesterday. What I'm trying to do is have Excel use today’s date on the computer to tell me when my Paydays are in a given month. Or have it tell me the next two or three Paydays based on Today's Date.
I can get Excel to calculate Every Two Weeks by using the formula =A3 + 14 then have each sequential field add 14 days to the previous fields date. However what I end up with is just a column of Pay Dates. Not what I'm looking for. I want to have an active page that only shows 2 or 3 future Pay Days. I want to use the function =TODAY() in my formula so that every time I open up the file it can calculate the next Pay Dates automatically.
I have been playing around with this formula:.....
All this does is tells me the First Friday of every month. This won't work for me because the Pay Periods are separated by 14 days not the 2nd and 4th Friday of every month. Here is an example of my Pay Periods: Sep 19, 2008; Oct 3, 2008; Oct 17, 2008; Oct 31, 2008; Nov 14, 2008; Nov 28, 2008; Dec 12, 2008; Dec 26, 2008; etc.......
View 5 Replies
View Related
Feb 13, 2013
Im having a hard time creating the formula to calculate the min number of employees to complete a number of jobs that come in each day and need to be finished in 18 days or less.
Data that I have:
Forecast of the number of jobs for 251 days ( jobs range from 10 -80 per day) Each job needs to be finished in 18 days or less we need to staff a fixed number of employees for the whole year each employee can complete 4 jobs per day no weekends
i have excel 2010 and crystal ball
View 1 Replies
View Related
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
View Related
