Array Formula: How Many Minutes Of Tickets Are Open Within A Particular Month
Mar 6, 2009
I am trying to write an array formula that will work out how many minutes of tickets are open within a particular month. I have a formula shown on the first table which calculates how many minutes a ticket is open for within a particular month, but this only works for an individual ticket, Basically I neet to populate the 2nd table with an array formula that will calculate the number of minutes within the month that numerous tickets are open, effectively a SUMPRODUCT but I can't get it to work.
The right hand side of Table one doesn't exist in my main file, I am trying to populate the 2nd table using only the first four columns within Table 1.
******** ******************** ************************************************************************>Microsoft Excel - My God.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF3G3H3F4G4H4F5G5H5F6G6H6F7G7H7F8G8H8F9G9H9F10G10H10F11G11H11F12G12H12F13G13H13F14G14H14F15G15H15F16G16H16=ABCDEFGH1 01/02/2008 Jan 08Feb 08Mar 082 3129313W00000220/01/2008 17:33:2629/02/2008 02:36:5456037 16,035.8540,001.15-4W00000127/01/2008 01:05:4620/02/2008 03:17:52200 41.13158.87-5W00000122/03/2008 09:06:1824/03/2008 08:33:57789 --..............................
I'm having difficulty creating an array formula. In a multi-column sheet, I am looking at a column with classes and a column with a date (in the format 7-Oct-09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes: =COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and =SUM((A4:A2500="AA")*(H4:H2500="??-Oct-??")) which gives me 0. Maybe an array formula is not the way to do this.
creating a formula for converting time data that has been created in an excel spreadsheet in minutes i.e. 516 minutes which I need to turn into Hours and Minutes i.e. 08:36 I am not experienced using Formulas, apologies if this question has been posted before, I did use the search facility to look for threads, but could not find anything related
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details: Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1) - $25K-100K, recognize in two equal parts in months N + 1 and N + 2 - over $100K, recognize in three equal parts over 3 months N + 1, N + 2, N + 3 ...
I run a report from a scheduling software that gives me the total hours and minutes for a weekday for my bus routes. For instance, 253 hours and 23 minutes for one day. Now though, I need to calculate how many hours and minutes for the entire month. The report will only come out with 253h23. I need to somehow take that, turn it into time and then multiply it by 20 (January weekdays). But I need real hours and minutes and I'm having trouble getting them.
I have made a form with a calender control, a keypad for hours and minutes. I have no problem getting the date from the active cell into a label caption and changing it by the calender, but I am having trouble reading just the hour and minutes into separate label captions from the active cell and changing them via the keypad and updating the active cell at the same time with a new date and time.
I have an excel file stored on a network drive for the purpose of information sharing. (File protected with a password)
But some the guys leave the file open for quiet long time and hence I cannot open the file for updating the data.
-I need to have a macro that runs every 5 minutes and displays an alert message saying "Please close the File" as long as the file is kept open.
-A second macro with a modified version of the above to close the file automatically after 5 minutes from file opening time after showing an alert message "You cannot leave the File Open, File is Closed Automatically!"
I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....
VB: Sub OpenWorkbooks() Dim WorkbookOpen() Dim WBnames() As String 'Array of WorkBooks to be Open Dim WorkbookCnt As Integer
There are 2 sheets, the 'Open Cases by Resolver' sheet is where I am trying to gather all the relevant information from the 'Open Cases - STATS' sheet. I need to be able to group the tickets in ages, ie tickets open Inc<30, Inc 31 to 60, Inc 61 to 90, Inc >90. The spreadsheet will help explain it in better detail. I thought a ' CountIf(And' formula might work (am trying to keep it formula based) but am having trouble recognising the team name then counting the ageing tickets.
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook
open macro-
Private Sub Workbook_Open() Dim dTime As Date dTime = Time If dTime >= TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub
This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
I have log data in two columns: Column A: Date/time (at 30 minute intervals) Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
I have a spread sheet with a colum showing average time to complete a task. This is currently shown as Days:Hours:Minutes:Seconds (4:19:33:19). I meed it to be shown purely as minutes, or at least as hours and minutes.
I have a formula which will calculate the number of hours and minutes between two military times. I would like it to calculate the total number of minutes instead of hours and minutes. I have uploaded a small example of what i have so far.
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
I had posted about updating the sheet name but I defaulted those to generic terms. I would like to see the month populate at the top of each sheet when opened, this is for a template.
Currently the title is in A1--merged with other cells to look nice.
"Sales Monthly Report - December 2006" is the title.
I guess I would default the title to "Sales Monthly Report-2007"?
I would want the On Open to insert the previous months name and year (if applicable) since I'm running this the month after the month ends so default to previous month. not every sheet in the workbook will have this just 10 out of 12 sheets.
I have been trying for the past four months now to get my workbook to automatically open a new worksheet every 1st of the month.
but I have failed each and every month. I have asked others to assist but to no avail.
this is the code in my workbook as it looks now.
Dim strValue As String Dim Sht As Worksheets, foundSheet As Boolean, TEMPLATE As String Dim rng As Range Dim irow As Long Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String Dim ws As Worksheet Dim strName As String Dim wsh As Worksheet
i keep getting an error on the activesheet=newsheet.
I have a macro that saves a workbook with the month name automatically generated in the filename with the code below (apologies for the scrappy code, but I am rather new to VBA)
In another file I want to re-open this workbook. However, the problem arises because this will not happen till the next month, in order to update the new month's workbook with the old information. Currently I am 'cheating' by modifying the code with the actual month name, like this
Workbooks.Open Filename:="I:EngineeringCapacity PlanningCapacity Report CONTROLS April.xls" I have tried to use the Dateadd function (with a -1 as the add amount) but I have failed to make it work.
I'd like to make a template so that when it is opened the previous month name is inserted in the sheet name. The reason I want to use previous month is that normally this report is created in the month following the month being reported. So it is opened, months are updated, then the user saves as xls. Ideally I guess sheet would not have the month on the template.
The name of the sheets are shortened months like "Dec Results Bob" and there are 8 of these.
I can tell it might start:
Private Sub Workbook_Open() (or would .xlt be the same?)
but that's about it. I made a macro of renaming sheet but that didn't really tell me if I could insert the Month there.
I'm trying to convert 3786 minutes to day:hours:minutes. So divided it by 1440 which is 2.63... but I want this displayed in the worksheet as 2 days 1 hour and 3 minutes (02:01:03), I just can't seem to get it to work and it seems quite simple... but I'm missing something.... I was trying a custom format like dd:hh:mm or [d]:hh:mm and I was also trying a convert function and =day/1440+hour +minute
I have a program that outputs a total amount of time that a service was provided. This time is reported in hours minutes [Example: 01.08 (hh.mm)]. I need a formula that will "round" to the closest .25 based on a 7 minute window on either side. Here how it would need to work....
I am trying to write a formula that will take the value of cell J9 which is in hours and minutes format (hh:mm) and return a value in £. ie. if J9 5hours and 12hours £15.00
I used this =IF((J9)5,(J9)24,(J9)>24),15,))) returns #Value! It won't work as it won't accept the hours and minutes format in J9. If I maually enter a value in J9 like 7.00 it returns the correct £12.00
All i want is to give solution for this time in and out.how to deduct late minutes to the salary of staff.
Date IN OUT Thu 4/3/20149:1418:39 Sat 4/5/20149:0117:56 Sun 4/6/20148:5817:58 Mon 4/7/20149:1217:50 Tue 4/8/20149:1618:00 Wed 4/9/20149:0216:06 Thu 4/10/20149:1017:54
I am using a formula such as =Text(A5-E5,"H:MM) to get the difference in clock-in time and clock-out time on a daily basis (Monday-Saturday). I want to add the results as a total for the week. I am not sure what formula to use to get that result. I prefer not to use decimals unless I have to. Also, the above formula does not work when the time goes past 12 midnight.
