Finding time duration between a start date & time with end date & time
Hi all! I'm looking for any formula or power query to calculate a total time duration within a day, given the start date, start time, end date, end time. Most of the dates will equal the same but there are some with the end date being the next day. I'd like to be able to exclude any overlaps as well. Currently, I have a large embedded IF formula: =IF(AND($G4=$O4,$H4<$H5,$P4>=$H5,$P4<$P5,$H4<$H3),$P4-$H4,IF(AND($G4=$O4,$G4>$G3,$H4<$H3,$P4<$P3,$H4<$P3,$P4<$P5),$P4-$H4,IF(AND($G4=$O4,$H4>$H3,$H4>=$P3,$P4>$P3),$P4-$H4,IF(AND($G4=$O4,$H4=$P4),0,IF(AND($G4=$O4,$H4<$P3,$P4<=$P3),0,IF(AND($G4<$O4,$H4<$P3,$P4>$H4),($P4+1)-$P3,IF(AND($G4=$O4,$O4<$G5,$O4<$O5,$H4<$P3,$P4>$P3),$P4-$P3,IF(AND($G4=$O4,$G4>$G3,$H4>$H3,$P4>$P3,$H4>$P3),$P4-$H4,IF(AND($G4=$O4,$G4<$O5,$P4>$P3,$P4>$H5,$H4>$H3,$H4<$H5),0,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3,$P4>$P5,$P2>$P3),$P4-$P2,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$H4<$H5,$P4<$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$H5,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3),$P4-$P2,IF(AND(ISBLANK($O4),ISBLANK($P4)),0,IF(AND($G4<$O4,$H4<$P3,$P4<$H4),($P4+1)-$P3)))))))))))))))) This seems to work for the most part but there are a few that I just can't get. I also pulled up my query and started to enter in the time durations manually and it couldnt come up with anything automatic for me. There must be an easier way for me to do this other than trying to create an IF formula for each answer that turns up incorrect. I have a screen shot below.
Learning Macro Control Buttons
My goal is to embed two control buttons that pop up when a certain range of cells is selected. I have two columns (C:D) that have numeric entries for quantities of items. I want to be able to select a cell in this range and have two buttons appear next to active cell, one to add by an input value and one to subtract by an input value. I was able to create Form Control Buttons that can do the task but I can't figure out how to align them to the active cell and hide them if the active cell is outside of the desired range. Any suggestions? Do I need to use ActiveX buttons instead of Form Control?Initializing TextBoxes within a UserForm
My spreadsheet contains a macro which utilizes two UserForms, each containing several TextBoxes. I want to initialize several TextBoxes in each Userform. One UserForm works and the other one does not, even though I have set the code up identically in each one. Here is what I have detected: When you step through the macro code (F8), when the first UserForm.Show statement is executed, the control skips to the first line of code for the UserForm and when the second UserForm.Show is executed, the control skips to the first TextBox on the Object. Here is a sample of the code I am using: Private Sub UserForm1() TextBox1.Value = "ABCDE" End Sub I have been writing macros like this for over 30 years, but this is the first time I have run into this situation. Any help here will be greatly appreciated. Al
Date of Last Modified for a Cell
Good day, all. I am inquiring if there is an excel function that would give me a date (and time) that an individual cell was last modified. For example: cell A2 has an entry of $2.55 and I'd like cell A3 to read "12/5/22 at 10am". a week later I change the $2.55 to $3.00....I'd like the A3 cell to read "12/12/22 at 10:30am". I've seen some VBA solutions but wondering if there is a simple excel formula to accomplish this?
Trouble Setting and Clearing Filters
Hello all. I'm on Day #2 of my VBA experience. I'm working on a spreadsheet in which I'm using a series of command buttons to filter the content my data. I have the following three subroutines - each associated with an individual command button. (ok ... I lied, there's about 20 buttons - each of which sets a different filter - but I can demonstrate my issue with just these three) When "Button A" is selected - it executes the following code: Sub xJitFilter1() Rows("14:14").Select Selection.AutoFilter ActiveSheet.Range("JitComp1").AutoFilter Field:=13, Criteria1:="1" End Sub When "Button B" is selected - it executes the following code: Sub xJitFilter2() Rows("14:14").Select Selection.AutoFilter ActiveSheet.Range("JitComp2").AutoFilter Field:=14, Criteria1:="1" End Sub When "Button C" is selected - it executes the following code: Sub ClearAndReturn() Rows("14:14").Select If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Selection.AutoFilter Range("xJitLanding").Select End Sub It works just like I want it to - IF I select the buttons in a specific order. For example: Button A --> Button C --> Button B Works perfectly However, IF I select buttons in a different order (i.e.,) Button B --> Button C Crash and burn: Run Time Error 1004: Autofilter Method of Range Class failed If I selection Button C (i.e., ClearAndReturn) when first opening the spreadsheet - prior to selecting one of the "Set Filter" buttons (i.e., Button A or Button B) - it's crash and burn with the same Run Time Error. Bottom LIne: IF I select any of my 20+ "Set Filter" buttons - and followed by the "ClearAndReturn" sub ... I can bounce between filters all day long. The "ClearAndReturn" sub without having selected a "Set Filter" button or try to go from one "Set Filter" button to another ... instand fail. I assume I'm failing to reset something in the environment ... but darned if I know what! Can anybody point me in the right direction? Thanks - SpaceNormanAdd VBA Code to an Option button dynamically
Hello Excelers, I hope you are doing well. I am back for some more help and here is what I am trying to accomplish: I have some code that would add x Option Buttons to a User form. x is Unknown but usually less than 15 but maybe more at times. I need to assign a macro that would return the newly created Option Button's Caption. In a Module I have: Dim objOptBtn as MSForms.OptionButton In a For i = 1 to x loop I have: Set objOptBtn = uFrmChoice.controls.add("Forms.OptionButton.1","objOptBtn" & i,true) With objOptBtn .Caption = i .snglTextWidth = Len(objOptBtn.caption) .width = snglTextWidth + 18 .Left = 10 .Top = TopLevel + 10 .Width = 400 .Height = 18 End With I remember there was an .OnAction "MacroNameYouWantToRunWhenOptionButtonIsClicked" But I do not see it in the Intelisense???? How can I assign code to run once the user clicks on a Option Button on the UserForm and return the Caption of that Option button? I am trying to ask the user to choose ONE option from a set of unknown options.How does Excel calculate named formulas and materialized named ranges?
I have been creating utility formulas with AFE to make my lambdas more readable and maintainable, for example: ManualAllocRng =OFFSET(ManualAllocCorner,1,0,URows,EntityCount); How does Excel calculate these results? Are they calculated every time I use them, or is there an internal type of materialization that happens once and then is re-referenced on each usage? In particular, I am wondering: (1) If I have a lambda that references my ManualAllocRng twice, does it matter if I write it as thing = LAMBDA(x,LET( rng, ManualAllocRng, a, something(rng), b, otherthing(rng), res, combine(a,b,x), res )); or as thing = LAMBDA(x,LET( a, something(ManualAllocRng), b, otherthing(ManualAllocRng), res, combine(a,b,x), res )); Does version 2 calculate the result twice? Does version 1? (2) Should I instead be materializing the value in a Calcs sheet, naming the corner ManualAllocResMaterialized, and using ManualAllocResMaterialized# throughout the workbook instead of referencing ManualAllocRes? Does every reference to the named formula calculate its result again? Does every reference to a spill recalculate the result, or does it "look" at the spilled range that was already calculated? On a related note, I have found a circumstance where sheet-based spilled result =Ledger.FilterMatch produces a different result from VBA Dim B as Variant B = Evaluate("=Ledger.FilterMatch") This seems like a serious bug. So I am considering materializing all my AFE-based named formulas onto a Calcs sheet just to avoid this bug, even if there is no performance issue to consider in the questions I asked earlier.
