Counting Total Number Of Days In Specified Period For Dataset
Sep 25, 2013
I am trying to find a way of counting the total number of days medical devices were in-situ for a fairly large dataset.
The worksheet has a few thousand rows. Column A - unique identifier for patient; column B - date inserted (Aug 02 - Aug 13); column C - date removed or audit date. There is no missing data, all rows have both dates. Some devices in for few days or weeks, some for up to 7 years.
I want to count each 12 month period (starting 1 Aug 02) the total "device days" for that year. (e.g. if device inserted 1 Feb 03 and removed 1 Apr 03, in year period starting 1 Aug 02 would have been in for 59 days. Another device inserted on same day and not removed until 9 Sept 05, would for first period (01/08/02- 31/07/03) 181 days, second period 366 days, third 365 days and fourth (01/08/05-31/07/06) 39 days). Therefore for the two rows, the first period total would be 240 days (59+181).
I am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range. For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.
Unfortunately, the formula above gives me the total number of activity and NOT the total number of hours within a specified period. I attached the file I'm working on as reference.
This list goes on with every workday of the month repeat several times, with no day have any set number of entries. Also each new month is just tacked on to the previous list. All of this is in column A but there are many other columns of data with these dates. I just didn't see the need to replicate that here.
What I need is to be able to count the number of unique days per each month but I do not want to have to specify a range for the month of January, then another range for February, etc. I just want to be able to list the column A4:A1200 and have the formula select just the unique days for January. Then in another cell modify the formula for unique days in February and so on.
I have got a project that has several hundred starts and finishes. I want to count them over time to use as an indication of progress. ( I want to show this graphically)
I have got the graph working but it is pretty long handed.
I have tried using a pviot table and picvot graph which almost does it but has a few problems (if there is no data in a month then it wont create an entry for the month, and the cumulative over time is not quite working).
Both examples are in the attached file : Progress Curve ozgrid1.xlsx
i am trying to get the number of days from last time ticket paid till todate, considering the day & month of the hiring date but the year of the last time paid. say AAA hired on 15-Nov-2001 receives a ticket every 24 months; received last ticket in Nov 2011.
i want to count the number of days between 15-Nov 2011 until 31-Jul-2013 (dd & mm are from joining date yyyy from last time paid) i have a list of 1200 names with different dates of joining and different dates of payments.
I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.
Captain America ................................A ..........................T .....................................T Incredible Hulk.......................................................................................... A Spider Man ........................................................................................................................ ........A Iron Man ....................................................T
( I added dots because it wouldn't let me space them out?)
We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.
Any formula to use. Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!
I have a Workbook with multiple sheets with Data in them. I would like to Count the number of duplicate values between the sheets.
For example.
Sheet one has a list of case references Person A is dealing with.
Sheet two has a another list of case references Person B is dealing with however the also have a list of case reference's they are monitoring as part of a project.
Sheet 3 has got the total number of case references each Person is dealing with, it also contain the total number of cases Person A&B together are dealing with. however i need to find the total number of cases Person B is monitoring that Person A also has so that the total number of cases is the true number of total cases with no duplicates involved.
(References begin with VB then have a string of numbers afterwards and I would like to know the total number of cases the Person B is monitoring as well so these need ot be calculated).
I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.
I have the below code to get the difference between todays date and the date located on sheet "requests" "B3". The code returns the value: -28040498
Code: Dim MyODateRng As String Dim MyODate As String MyODateRng = Sheets("Requests").Range("B3").Value
[Code]....
So i gues with the first code there is something wrong with the date formating. But i do not understand why the second code does not return simply return the value: 1.
I have a list of about 2,500 entries containing information of all my works leave requests for 2009. This list contains more than one entry per employee, as it shows all their individual requests for different weeks. The list is compiled pulling through data from other worksheets. It looks like this...
Sheet Name: MASTER
Column A = Line Managers Surname Column B = Employees Surname Column C = Employees First Name Column D = Employees ID Number Column E = Week ending Column F to L = Days of the week Column M to O = Request Type Column P = Number of days requested for the week
I then have another sheet (Called REQUESTS) with all the employee names (1 entry for each employee), ID numbers and total requests for the year. This looks like this...
Column A = Line Managers Surname Column B = Employees Surname Column C = Employees First Name Column D = Employees ID Number Column P = Number of days requested for the year Column Q = Number of days requested for the summer
I know how to locate and add up all the requests for each individual for the whole year (Column P) from the MASTER sheet to the REQUESTS sheet by using the flowing formula for each employee.
=SUMIF(MASTER!$D:$D,D2,MASTER!$P:$P)
But I don't know how to ask Excel to find and add up all the total requests for only the summer period (Column Q). What I want Excel to do is this................
I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.
I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following: Within a workbook the 1st sheet is the data entry. In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example: Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry. G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
I have a range of dates in c1:c285. I have a range of liters sold on those days in e1:e285. I want to total the liters sold per month for the 2 year period.
I was trying to use =SUM(IF(MONTH(c1:c285)=1,e1:e18,0))
However this just returns a "value" comment. I think even if this worked it would add the months together for the 2 years so both June figures would be returned as one figure.
I'm trying to count the number of rows in a dataset where column A is something, B is something, C is something, D is not something, D is not something else, and column E > 0 OR F is something but I just can't get the result I want.
My actual dataset is a staggering 294,000 rows and 46 columns so here's a simplified version:
I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
Imagine that i have an item entering my warehouse on 22/05/2008 at 21h35 and leaving on the 25/05/2008 at 5h42.
A1= 22/05/2008 and B1= 21h35 A2= 25/05/2008 and B2= 5h42
The goal is to count the full 24 hours day and the remaining hours.
For the example given i can say that on day 22 the item only says 2 hours and 25 minutes, on day 23 it stays 24 hours and on day 24 another 24 hours, on day 25 the item leaves at 5h42, so it only stays those 5h42.
So we have 2 full 24 hour days and 2h25 plus the 5h42, the the item were stored 2 days and 8 hours and 7 minutes.
The problem is when the item arrives at (example) 22/05/2008 2h00 and leaves at 25/05/2008 23h00 on another day. Lets say that on the first day the item is 22 hours stored, plus the 2 full 24 hour day and another 23 hours. So it is 2 days plus the 45 hours, that is 2 days plus 1 day and the remaining 21 hours.
how to add to this 2 full days the 1 day and 21 hours
Right, Column A on my sheet is the date that the work was completed. First thing is I need to be able to count how much work was completed per month. (I dont know why we have it all in one big sheet rather than monthly sheets but thats too logical!)
Secondly, column I is the number of days that piece of work took to complete. I need to be able to have a formula that looks at the month the work was completed, then total up the number of days that the work took.
(Example, if I have three bit of work completed in October, 1 took a total of 20 days, 1 took 15 and 1 took 10 it would = 45 days)
I know that this should be possible as I've had Excel doing more complex formulas than this.
I have a problem now with my new spreadsheet. I need to use vba to automate a process.
Date Item Holding Days
01/03/2014 A 1
[Code] ........
Scenario: On 01/03/2014, I bought A and hold it therefore holding days is 1. Then I sold it on 02/03.2014 so it became 0. Until 04/03/2014, I bought A back, therefore holding 1 day. 05th I didn't sell so I am still holding it, meaning holding days =2. Then on 06th, I sold A and buy B, resetting the holding days to 1 for a new item.
From the table above, how can I use vba to automate the last column?
number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
I have converted a column of dates to days using format & dddd. However I'd now like to count how many mondays, tuesdays, etc are contained in this list, however as the data is still a date I cannot seem to do this.
