I'm trying to calculate the length of a work order to develop an average and future proposal estimates.
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.
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.
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
If I had two dates in two separate cells , so E2 is the 01/10/08 and F2 is 06/10/08 and I want to work out that their is a difference of five days what would the sum be? Also is there anyway I could factor into that sum what is pure working days as opposed to weekends?
I need a formula that will allow me to put a date in cell a2, and in cell a3 put the the number of days between 2 dates. Example.. For example (A2) shows 08/06/08, a3 to show the number of days from 08/06/08 to 08/14/2008--(a3 )14 and (a4) to shows the number of days from 08/06/08-10/05/2008 ---(a4) 60 days.
I have a spreadsheet where I have an intial date of issue of a warrant. Once this warrant has been executed, then a further column is populated with the date. The data I need to work out (Preferably without using macros if possible) is, if there is no execution date the length of time from issue to todays date, but if there is an execution date then length of time from issue to execution. I can do either on their own, but i would like to have only one column showing this info and if possible to update each time the sheet is opened
At the end of each month, my agency collects data about clients served in our programs. One of the many pieces of information they want is the number of "bed days" a client was residing within our programs for the given month.
Jon Doe entered the program on 7/16/13 and discharged on 8/15/13. I would like to know how to calculate the number of days in July that Mr. Doe was residing in the program. (The date of discharge is not included.)
I recently took over the responsibility of completing reports such as this one at my office. The previous person who completed the monthly reports would count the days by hand for each client, but I know there must be a better way.
I need a formula that will calculate the number of days (ex from 10/1/08-1/1/12) and will differentiate between leap year and a regular year. I am trying to calculate interest between 2 dates and leap year is calculated on a different interest rate. Right now I have it set up so I have to manually type in the leap years in a separate field instead of being able to just you point a to point b.
I have half hourly data for five years, in two columns A: Date and B: Load in Kg. The load on the beam is not uniform over time and shows a cycle of load.
In Column C I have an AVERAGE function that returns the average load based in the 4 hour period (2 hours backward looking and 2 hours forward looking) if it is less than 50% of the maximum load.
The data in Column C returns sets of continuous vertical cells of data showing periods of low loading and empty cells where the load is greater than 50%. The sets can be as small as one cell (i.e. one hour) to a set of contiguous vertical cells for one week of low load.
My objective is to use Excel to analyse all the sets of contiguous cells in Column C to determine the time period from the first cell of each set to the last cell of that set and drop the result into another work sheet (pivot table) to further analyse these results.
I am attempting to create a formula that will count the number of times, lets say letter A, occurs in column E. However, the tricky part that I have been confused on is that I only want to count how many times A occurs between each hour of the day. I need to count number of times "A" occurs between each hour of the day for the entire month.
My Worksheet looks as such: Column A contains the dates for the month of November, Column B contains times that random instances occur, Column C is not important, Column E contains a Letter A-N that pertains to what occurred at a time in column B. SO, Column B and column E coordinate with each other.
Here is an example. Column A - Cell A1 - 11/01/2013 , A2 - 11/02/2013, A3 - 11/03/2013. Column B - Cell B1 - 12:01 AM , Cell B2 - 14:03 , Cell B3 - 15:23 Column C - Cell E1 - A , Cell E2 - A, Cell E3 - B
So I would like Column F to display that between 12:00 - 12:59 Am there was X number of times the letter A occurred throughout the entire month.
(1) I want to find the maximun number for the past n periods in a range of cells, but I want to be able to vary n by changing the number of periods in one cell at the top of the sheet, i.e. if i enter 10 in cell A1 the function will return the max number for 10 periods over a certein range, if I change the figure to 20, then the max number for 20 periods will be returned!
(2) I have a column (lets say column c) which will return a number of values if my criteria are met and 0 if not, I want a function in another column which will replace the 0 with the next non 0 figure in column c, the gaps between the non 0 figures can vary. I feel it involves looping in some form, and writing a new function, but I am not sure.
What I would like to do is insert a formula into column E that would calculate the number of days from when the count is at 50 to when the next count is i.e. for part 6689841 the time between when the count was 50 and when it was at 20, so the difference between 21/01/2008 and 08/08/2008 in the above example. It does not matter what the count number is (it could be any number except 50), I'm only concerned with every time the count is at 50 how long it is until the next count and to show the result in days.
I am trying to calculate the number of days that items are were held in inventory as of a given date. See below for some sample data.
'As Of' - the date in question # Holdings - Using sumproduct to find items bought on or after the date AND sold after the date Held Days - ? Total number of days that the six items were sitting in inventory as of the date.
As Of #ItemsHeld Days 5/21/98 6
ItemBoughtSoldTotal Inventory Days 19/3/919/3/961827 21/2/921/2/971827 31/2/921/2/971827 43/2/928/21/951267 55/1/925/1/971826 65/1/925/1/971826
I am trying to calculate the time elapsed. I have included a caption to show my formula. The problem I have is that some times are showing > 60 minutes instead of increasing the hour. When my day go over the midnight hour I get a negative number.
I recently manage to create a spreadsheet. On the spreadsheet what I am looking to do is once I change the year in cell U1 from 2010 to 2011 to automatically change the days and the date number, and where Sat and Sun preferably to auto-fill in yellow the whole column within the table as you can see in the spreadsheet.if not then just do not display Sat/Sun columns at all..
I am trying to calculate dates that will happen 16 days from date specified in column A (A23:A200), based off a certain entry in Column B (B23:B200), which is named in cell P5. I can get the date by doing the specified date + 16, e.g. =IF($B$23:$B$200=$P$5,$A23+16,"")
The trouble I am running into is not having the date populate until it has reached 16 days from the dates in column A, show blank if possible until 16 days from date in column A. The dates in column A will always be previous to todays date.
I have a static date and military time in B6 (5/10/07 18:00) I have to write various formulas to reflect 1 month (whatever calendar month it ends up in, so not necessarily 30 days) minus 7 days and then the same formula plus 7 days. How is this written?
I need to create a formula that calculates the number of days a person has worked within a quarter, if they have also left the organisation within the same quarter.
For example, I have someone who left on 26/08/08. I need to establish how many working days this person actually worked within the quarter (01/07/08 - 30/09/08). I need to do this for a large number of staff, so would appreciate it if anybody can let me know whether there is a formula that would calculate this.
I have a file that's almost 3000 rows of data I have attached a test file for the purpose of this request. I am trying to figure out the average days overdue per department whose due dates have not already passed.test (1).xlsx