I have a formula that calculates only seconds and frames. The frame rate is 75 fps (0-74).
e.g., 49.50 is 49 seconds 50 frames. It will not parse Minutes.Seconds.Frames, e.g., 1.49.50 is 1 minute 49 seconds 50 frames.
What my existing formula does is converts the number to all frames then converts the answer back to seconds and frames.
I need this formula to include minutes in its calculation.
A copy of the spreadsheet is here: ...
I need 15 minutes removed from a difference in time, if the time span falls over 9:00 am through 9:15 am.
I'm inputting time like 8:30:00 in A1 and 10:30:00 in A2, then calculating the difference in A3 (2:00:00). Is there a way to remove 15 minutes from that 2:00:00, since it falls over 9 to 9:15?
How to create repeated time frame within a column like example stated as below:
Time 9:45AM
10:00AM
10:15AM
9:45 AM
10:00AM
10:15AM
9:45 AM
10:00AM
10:15AM
I need to be able to calculate the actual number of days worked for an employee over a specific date range. I have a data sheet containing employee, transactions, and date of transaction (date only, does not include time). The actual number of days worked may fluctuate due to time off or holidays, so I need to be able to calculate this individually by employee. For example, with columns Employee, Transaction, & Transaction Date:
123 ABC 07/01/2009
123 ABC 07/02/2009
123 DEF 07/02/2009
123 GHI 07/02/2009
123 ABC 07/03/2009
123 ABC 07/07/2009
123 DEF 07/07/2009
123 ABC 07/08/2009
123 ABC 07/09/2009
etc....
Should return 4 work days for employee 123 over the date range 07/01/2009-07/07/2009
I am trying to do a timesheet spreadsheets that lists employees clockin and clockout times
Name Start End num hours worked
Cory 02:00 04:00 2
Jack 23:00 05:00 6
Fred 10:00 17:00 7
and then go through the list and and count the number of employees in a certain range.
time range number of employees working
05:00-06:00 1
06:00-07:00 2
07:00-08:00 6
08:00-09:00 5
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00..............
I am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.
View 1 Replies View RelatedIm trying to count people working during times of the day. They work shifts from 8am to 9pm. I need to know at each 30 minute interval of the day how many are working. IE at 12:00 or say 13:30 I need to know how many are working. I also need to be able to account for people that are sick. IE If a person works during the day I wouldnt count them as sick at night.
Im data is set up like
Column A agent name
Column B agents start time
Column C agents end time
Column D holds two variables either sick or support.
I have found a thread that counts the people working but doesnt account for the sick or support variables. I didnt link that thread as I thought it went against the rule "STATE WHAT YOU WANT TO DO, NOT WHAT YOU THINK YOU NEED TO ACHIEVE IT "
Rather than manually typing the time in 5 min increments across a range of cells, i want to have a start time, then the next cell add 5 mins. EG: 09:00 next cell 09:05 and so on.
View 7 Replies View RelatedI'm using excel 2010 on a pc. I need to create a calender. So far I have been using the tutorial for the pop up calender. The result I'm looking for is to set up a specific time frame and have the calender provide the "due" date. I would like the due date to exclude weekends and holidays. Would I be able to do this with the pop up calender?
View 3 Replies View RelatedI am trying to calculate a new base pay rate, but I need it make sure it is at least brought up the new minimum and capped at the new max of the range where applicable. The increase is based on 10%
So here are the columns used:
L = Current Base Pay
Q = New Min of the range
S = New Max of the range
U = where I want to calculate a 10% increase of L, but ensuring it is brought up to at least the min (Q) or not over Max (S). In other words if my min is $12 and Max $18 and my new base pay is $16 -- then I am good. However it if is $11.50 I need the formula to return at least $12. And visa versa -- if the new rate would be $18.50, I need it to return no more than $18.
I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
dear friend in my document column "L" has some numbers & formulas.if any cell has formula base in that i need to inset rows below that formula cell & that formula need to spread on that new rows.i have 4 type of formulas.each formula has (1.5).that part is common. it 's like this...
(01.)ex- L1 cell =150*2*1.5 ,need to inset one row below this cell & after running the macro it should change like this..
L1 cell =150*1.5
L2 cell =150*1.5
(02.)ex- L1 cell =150*2*1.5+50*1.5 ,need to inset two rows below this cell & after running the macro it should change like this..
L1 cell =150*1.5
L2 cell =150*1.5
L3 cell =50*1.5
(03.)ex- L1 cell =150*2*1.5+130*3*1.5 ,need to inset four rows below this cell & after running the macro it should change like this..
L1 cell =150*1.5
L2 cell =150*1.5
L3 cell =130*1.5
L4 cell =130*1.5
L5 cell =130*1.5
(04.)ex- L1 cell =150*2*1.5+130*3*1.5+20*1.5 ,need to inset five rows below this cell & after running the macro it should change like this..
L1 cell =150*1.5
L2 cell =150*1.5
L3 cell =130*1.5
L4 cell =130*1.5
L5 cell =130*1.5
L6 cell =20*1.5
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies View RelatedI have a problem here:
Eg.
A B C D E F G H I
1 8 pm 9 pm 10 pm 11 pm 12 am 1 am 2 am
2 8 pm 11 pm
3 8 pm 2 am
I typed a formula : =if(and(c$1>=$a2,d$1
I have a UserForm (UserForm61) where I have a popup calender acts like DTPicker. and some other controls . My Problem is whenever I am trying to open the calender it is opening behind every control. changing the z order played no role.
View 9 Replies View RelatedOn an Excel 2003 spreadsheet I already have two option buttons. I need to add two additional optionbuttons. I placed them within a frame so that they will function independently of the other buttons. Specifically, I created Frame1, selected Frame Object-->Edit, select Toolbox, add Optionbutton3 and Optionbutton4, exit Design mode.
The new Optionbuttons correctly toggle their values when out of design mode . When I re-enter design mode and double-click to take me to the VBA code, it however only shows me Frame1_Click(). Within Frame1_Click() I have tried adding the code
Is it possible to distinguish a checkbox that is inside a frame from one that is outside it? What I am trying to do is loop through all the checkboxes on a multipage but only those that are within a specified frame.
View 4 Replies View RelatedFor Each ctl in Frame1.Controls
'Do whatever
Next ctl
I also know you can limit the controls you diddle by doing:
For Each ctl in Frame1.Controls
If TypeName(ctl) = "TextBox" Then
'Do whatever
End If
Next ctl
But this gets absurd when it gets to the point I want it:
For Each ctl in Page1.Controls
If TypeName(ctl) = "Frame" Then
For Each ctl2 in ctl.Controls
If TypeName(ctl2) = "OptionButton" Then
' Do whatever
End If
Next ctl2
End If
Next ctl
Is there any way to simplify this? Can I define ctl as frame, for instance, and then it will only look for every frame object in Page1? Or is there some other trick I'm missing? Or am I just making userforms with too many wrappers?
I am developing a form for emergency dispatching. One of the features of this dispatch sheet is that there are several maps in it that open on a seperate form. The map form has 4 maps that are picture files in an image. these images are in a frame. The different images are selected using option buttons. Given the background above, my problem is this. I have code that zooms in 50% each time the Click event for the image is fired. wht i am trying to do is make the zoomed view center where i clicked. this is the code that i have so far but it doesn't work all that spectacular, the closer I zoom in the further out of center the place i clicked gets untill it is out of view.
Private Sub Layout_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CurrentX = X
CurrentY = Y
End Sub
Private Sub PlantLayout_Click()
If Frame1.Zoom < 400 Then Frame1.Zoom = Frame1.Zoom + 50
frmmap.Frame1.ScrollLeft = CurrentX - (Frame1.Width / 2)
frmmap.Frame1.ScrollTop = CurrentY - (Frame1.Height / 2)
frmmap.Repaint
End Sub
when i input the arrival time of A Car, the time he arrives late appears in column D it should read 15 Minutes. how i can get this time to appear automatic after i have registered the arrival time. A Car due at 19:00 arrived at 19:15. 15minutes late. I would be grateful if you could lead me in the right direction.............
View 3 Replies View RelatedI have a form that has object buttons in a frame. I see that I can only select one or the other of the object buttons that are in a frame. Is it possible to see if no object buttons were selected in a frame and then display a msgbox telling the user that they need to go back and pick a button?
View 4 Replies View RelatedIs it possible to scroll down in a UserForm Frame with a VBA Code in excel. My frame in the userform has a scroll height of 600 and a lot of option buttons. Based on a selection in a ComboBox(using If statement), I'd like to scroll down to a certain amount in the Frame.
View 7 Replies View RelatedI seem to have a bug i can't quite figure out with my code - its causing excel to crash each time it reaches this line (when its commented out - the form runs fine - otherwise "excel has encountered an error")....
Set frameFullX = frameFull.Controls.Add("Forms.frame.1", "frameFull1")
frameFullX is declared as a control and frameFull is a frame already on the userform.
Anyone know what I might be doing wrong (I'm going to try restarting and see if was just an issue with my machine or not?
I have a sheet with some normal cells and then some MS Forms 2.0 frames with OptionButtons inside of them. If I have OptionButtons outside of the frame I know how to read their values, but how to do it when the control is in the frame?
I need to make a form on one sheet and then make as many copies of the sheet as needed, and finally I have a macro that copies the data from all forms to one sheet, so that each form represents one row. My main goal is to save that one sheet as a .csv-file and then import it to our CMR database.
I'm not very familiar with Excel forms. Should I take some other approach to this problem or should I continue like I'm doing? I would be almost ready with my workboot if I only could read the values of the optionbuttons inside the frame.
I have tried:
"If Sheets(i).FrameInstallationType.Controls.SL_STD_HD.Value = True Then"
and
"If Sheets(i).FrameInstallationType.SL_STD_HD.Value = True Then"
where "SL_STD_HD" is the name of the OptionButton, but apparently these both are wrong.
In Excel 2000 there seems to be a problem with setting the focus in a UserForm TextBox if the SetFocus method is applied in the same submodule as the UserForm.Show method. Microsoft's circumvention for this is to put the SetFocus command in the form's Activate event submodule.
This seems to work OK except when the TextBox you are applying the SetFocus to is the first control inside a frame. It works if the TextBox is not the first control, and it can be circumvented by first setting the focus on a subsequent control then switching the focus to the intended control.
However, the circumvention is not that useful if there is only one TextBox control in a frame or, as in my current project, if you try to create a generic piece of code to validate controls from multiple forms and set the focus from within the generic code.
Does anyone know of a way over overcoming this problem and being able to directly set the focus to the first TextBox inside a frame?
On my userform I have a frame. Within the frame, there are 6 option buttons. Can I trigger an event when when the user selects a different option button? I was expecting to see a Frame.Change method. But I don't. One solution is to insert code on each of the Option Buttons Change methods.
View 2 Replies View RelatedThe code below works really great which Andy helped me with, but now I've put the textboxes that are changing in a frame. That is because I need to scroll the textboxes. That works in another form I've got, but for this form I've also got the textbox, TBSum601, which is updated from the sub UpdateTotal() (see below). BSum601 is placed on the form but not inside the frame. When I run the application it stops in Sub TxtGroup_Change() at the line: TxtGroup.Parent.UpdateTotal
I think it's because of that the textboxes for the TxtGroup now are in a frame instead of as before, on the form. don't know exactly what the "Parent" does, but I think it's something I have to do with the code there.
Originally Posted by Andy Pope
Class event
VBA:
Private Sub TxtGroup_Change()
If Me.TxtGroup.Text = "" Then
Me.TxtGroup.Text = 0
End If
TxtGroup.Parent.UpdateTotal
End Sub
Public Sub UpdateTotal()
Dim lngTotal As Long
Dim lngIndex As Long
For lngIndex = 1 To UBound(X) / 4 ..................
I've got a column of numbers that represent the number of overs bowled in games of cricket. Whilst these are whole numbers (eg. 34 overs + 34 overs) the addition isn't a problem, but when they are incomplete overs (eg. 34.4 overs + 34.5 overs) then the addition if out of kilter as it sums them in base 10, and not in base 6. (As there are six balls in an over, not ten for anyone who doesn't know!)
View 2 Replies View RelatedI have placed a Frame Control on Excel Sheet and add Some Controls(TextBoxes) to it. What I want to Do is when I enter Value in TextBox1 and the Focus Got to TextBox2
Then Value from TextBox1 will place on Activesheet.cells(1,1)..
Please check the Attached File : FrameControls.xlsm
In the attached on the quote tab in cells G5 & H5 i have two formulas both trying to pick up the data from the highlighted matrix in the frame tab.(i only need one)
From the dropdowns in cells F1, F3 & F5 in the quote tab I wish to get the data from the matrix in the frame tab cell range C3 - F20