I have a worksheet where I calculate the duration it takes for an operation. the format of the worksheet is as folows:
The worksheet is composed of Start and Finish times in this format: 2009/06/02 5:32:19 , so for example,
- Row1 of the worksheet will have "Start" in column H1 and the start time in column B1
- Row 2 of the worksheet directly below has the "Finish" time, with the Finish time information in column B2
So with the help of excelforums I was able to implement a macro to extract the finish time cell found in row B2 and offset it to C1 and do a simple calculation to get the duration in minutes and display it in column D of the same row as "Start".
What is the formula for calculating duration in min/hrs between start time and end time without mentioning any date, and if the end time stretches to next day i.e beyond 12.00 midnight. And what is the logic used in the formula.
I have searched and think I have found what I need to create Outlook tasks from my Excel spreadsheet. I'm hoping that someone here can help me pull it together into something that will be usable in my project tracking spreadhsheet...
I try to keep track of milestones... these milestones are listed in column B of my spreadsheet and are in rows 10 through about 105
For each of my customers I then keep 4 columns D, E, F, G - H, I, J, K - L,M,N,O - etc. Each group of 4 is a different customer....
The customer's first column (D, H, L, etc.) is a Due date of the Milestone that is found in column B. The second column is a check box that I use to indicate when the milestone is complete The 3rd column is the completion date The last colum is just a filler space that helps to separate each customer.
What I would like to do is insert a button at the bottom of EACH grouping of 4 that when pressed would look at the first column for the customer group and for ANY Milestone that has a DUE date process the stuff in the VB below to create an OUTLOOK task for each item that has a DUE date. (It would be conceivable that as it stepped through each row for a customer group of columns that I could have it create 90+ Tasks in Outlook.
A couple of additional things... in row 110 for the first column of the customer I have a DATE or a blank. So for example in D110 it might be blank or a date like 10/29/2007. If it is blank then when I press the button it would loop through ADD the tasks for each row with a due date. If row 110 has a DATE I would get a popup indicating that the tasks are already in OUTLOOK and that they were added on whatever the date is in row 110... This is kind of the safeguard that I don't get the same group of tasks multiple times by pressing the button by mistake.
Im having trouble with getting a macro to run at a specific time and day (Every Thursday at 7PM).
Here's what I have done so far..
1. I added my excel file to the task scheduler 2. created a class module and place the code below into it.
In testing the workbook opened but it asked if i wanted to enable or disable the macros - how do I get around this? And it does seem to kick off the macro (create_all).
Private Sub Workbook_Open() If Hour(Now) = 1130 And Weekday(Now, vbMonday) < 7 Then ' TheNameOfYourCurrentProcedureHere create_all If Workbooks.Count = 1 Then Me.Save Application.Quit Else Me.Close True 'save the changes and close the workbook End If End If End Sub
I have a worksheet that runs a query from an external data program. I have made a macro that refreshes the worksheet and this works ok. Is there a way I can get Scheduled tasks to automatically update this workbook eg overnight. I do not want to leave the workbook open either. Can task scheduler open the workbook, refresh the data, then close the workbook again.
I scheduled a task to open excel and when it does (at a certain time), a little sub is supposed to run (couple minutes later) via the OnTime Method located in the Workbook Open Event. There is one weird thing though....
1. When Excel opens automatically through Scheduled Task (and you see the clean white sheet WITH gridlines), Sub does NOT run (that is, my file is NOT even opening).
2. When I myself open Excel manually WITHOUT opening a new workbook (in other words, in front of you there will be grey area without gridlines), Sub runs perfectly (in other words, my file opens automatically and Sub performs whatever it is supposed to do).
I am trying trying to calculate the amount of time students view videos. I understand I should use [h]:mm:ss but excel continues to convert entries such as 0:14:59 (0 hours, 14 minutes, 59 seconds) to 12:14:49 PM both in the cell and the function bar.
The problem gets complicated because I have entered the data with two different formats; some in h:mm and some in h:mm:ss'
1) Is there an easy way to sum the duration time so it accurately reflects total hours:minutes:seconds? 2) Do I need to reformat or re-enter data to make it work? 3) If so, is there a macro to do it?
This has been a bear, (40 students by 30 videos) all hand coded.
I have the below Date & time in cell a1 and another date and time in cell a3
8/2/2010 08:45:00 8/2/2010 10:15:00
I need to be able to show duration (in hours) between the 2 times, in this instance its the same date, but this wont always be the case. To add a level of complexity in addition to the gross duration in hours, I will also need to know how long (in hours or mins) of the duration fits into "in hours" ( 9 -5 mon- fri) then the duration thats fits "out of hours" (after 5pm weekdays or all day Sat & Sun)
Over the weekend I had to look at 220 strings of numbers, some strings with as much as 20 numbers and determine if the numbers represented red, green, blue, or yellow and how many of each color from a parent list.
I did it all by hand. After getting help here from JBeaucaire on my tally sheet, which I successfully recreated with their guidance, I thought I might ask this question. How would one go about creating a macro that would when it seen a certain number, it would put the color in the column immediately to the left, and the number of colors in the column immediately to the left.
I have a project network spreadsheet shown below and in column BT I need to identify each non-critical path task (value in column BS is "N") that has a critical path task (value in column BS is "Y") as one of it's immediate predecessors.
Predecessor tasks are listed with commas separating them in column G. They are also separated out into columns H through Q for another function in the spreadsheet.
I was given the following formula by Domenic some time ago that I use along with VBA code supplied by Fin Fan Foom to open do a lookup on a closed workbook:
=If(ISNUMBER(MATCH(MIN(If( Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(If(Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),"") Everything works fine, until I attempt to… - cut & paste a block of cells, or - do a SaveAs, or - change a worksheet name
The formula will no longer function after performing any of these tasks (the cells go blank), and I have to close the workbook without saving to preserve the original workbook functionality. All other tasks, including even importing of different files into the workbook have NO negative effect.
The formula uses an “INDIRECT” named range. Sheet name changes are reflected in the named ranges, but any change of names somehow causes a malfunction (no sheet names are referenced in the VBA).
I thought that the large lookup workbook (40,000 rows) may be a problem, but even after deleting most of the data and retaining only a few rows, the problem still remained.
After the formula cuts out (after performing the above tasks), if I open up the lookup file, then the lookup will work again, but if I close the lookup file, it will stop working.
I doubt the problem is in the VBA code since IT WORKS, and besides, I have other simple VLookups formulas that also uses the same process (and VBA code) and they continue to work just fine when that one formula gives out. The only thing I can think of is that the complexity of that one formula may be an issue.
Anyway, I the original thread is long, so I’m starting a new one here. The original thread is here (Domenic’s formula, pg 7; FFF’s code, pg 8): Dedicated Cell To Choose Lookup Table
I'm trying to create a work sheet with start and ending times with a 30 minute meal break, for the end of the week I would like to calculate those hours as a general number.
I'd like to write a VBA script which writes all data of my mp3s in a spreadsheet. I can read all data like the artist, songname, album, year, this works fine. However I'm not able to get the duration of the mp3 files. Any sample script which reads the duration of the mp3 files?
I have following data, data_in and date_out. I need to auto cal these 2 variable different and give the output as duration in month.
x_INV EF1date_lndate_out205/10/195005/10/1950325/10/195025/10/1950415/11/195015/11/1950508/01/195029/10/1950618/10/195018/10/1950717/10/195017/10/1950817/10/195017/10/1950915/10/195020/12/19501013/12/195013/12/1950 Excel tables to the web >> Excel Jeanie HTML 4
And give the result in colume X as following :-
x_INV X1stk_dur20304051060708092 Excel tables to the web >> Excel Jeanie HTML 4
I've recently been experiencing some strange phenomena in relation to public variables. It seems that if I run separate routines in quick enough succession (e.g. press a button twice within a couple of seconds), the memory of the public variable "hangs over" into the second routine.
The routines, however, are completely separate events. And I'm not using userforms or anything like that in order to keep VBA running in the background. In which case it seems that Public variables don't always get set to nothing with the end of the final sub in a routine. Has anyone else experienced anything like this? I was under the impression only Static variables could do this.
Is there a format for duration of time. The attached spread sheet has column B "Duration". This is the actual time on which I need to do calculation. On "C2" I have the formula "=IF(B2>10,400,B2*40)". The formula is " if value in B2 is more than 10 hours then the value in C2 should be $400 , else C2= B2(duration time) multiplied by 40.
I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between, simultaneously keep the numbering in sequence only after the repeats 2 rows in between, and perform the previously mentioned 2 tasks any row in a column. So far, I have to manually enter or when I select cell X4 and X5 together ( having a value of 1 and 2 respectively) and drag, the value does not repeat 2 rows in between and does not keep the numbering in sequence after the 2 rows.
At Present: X4 1 X5 2 X6 3 X7 4 X8 5 X9 6 X10 7
I need to have
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ] 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+1 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+2 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+3 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+4 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+5 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+6 3 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+7 3 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+8 3
and so on...
I have a 2000+ rows to manually input and hence it is not practical!.
I have had some help with this (thanks!) but am stuck.
Can someone add to the code below and make it so that multiple tasks, with multiple assignees, can be created from a range of cells, such as:
Column A Column B Row 1Task SubjectAssign to Row 2GV LP for AZJohn Brown Row 3GV LP for COJohn Brown Row 4GV LP for ILSuzy Smith Row 5GV LP for INSuzy Smith Row 6GV LP for KYSuzy Smith Row 7GV LP for MNSuzy Smith Row 8GV LP for MSBob Barker Row 9GV LP for NCBob Barker Row 10GV LP for NJBob Barker Row 11GV LP for NMBob Barker Row 12GV LP for NVBob Barker Row 13GV LP for NYBob Barker Row 14GV LP for OHBob Barker Row 15GV LP for SCBob Barker Row 16GV LP for WVBob Barker
(Sorry, it didn't translate well. The "GV LP for AZ" is cell A2, "John Brown" is cell B2.)
I have a Log In/Log Out report that I need pulling data from.
Ultimately what I want to do is get the amount of time my employees were logged out.
I am using Excel 2003. Attached is an example of the data I'm working with.
Sheet 1 contains the data, and Sheet 2 will be the summary.
So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.
If there is nothing in the next row down in column D, then do nothing.
I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.
I'm trying to auto input names from my schedule into dailytasks for my servers. Sometimes the amount of servers on a specific day changes(i.e Mondays 3 to 5 servers and Friday - Sunday there is 4-6 servers on) and times could change also as of now it works with
=VLOOKUP("6:00",AC$25:AJ$46,8,FALSE)
Not sure how to set up lookup value and return the name
