Issue in Days Function
Hello there, I am Balaji, I hope you are all doing well. I am using Windows 10 in Dell Latitude 7480 Laptop. I am facing an issue using the "Days" Function. DAYS Function Explanation and Description mentioned in Excel Software: DAYS FUNCTION This article describes the formula syntax and usage of the DAYS function in Microsoft Excel. For information about the DAY function, see DAY Function. Description: Returns the number of days between two dates. DAY function This article describes the formula syntax and usage of the DAY function in Microsoft Excel. For information about the DAYS function, see DAYS Function. Description: Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. Now, If I am using the "DAYS" Function to calculate the number of days between two dates. Suggest me Which Function should I use to get the correct answer. I am Looking forward to your guidance. Thank you.
VSTO Excel Worksheet Scrolling Event
Hello all, I am trying to get an event from Excel when a user is finished scrolling using the scrollbars, the mousewheel etc. Now I could not find any Excel events, so i tried using API's. Basically a window procedure that intercepts scroll messages like msg = WM_HSCROLL OrElse msg = WM_VSCROLL OrElse msg = WM_MOUSEWHEEL etc. This works to get an event when the user starts scrolling (so not when the user finishes). So I need to add a timer and guess when the user finishes scrolling. Now I personally don't like using timers for things like this. Any Idea's? If required i can give you the code for detection of the start of the scrolling. I am using
Filter context playing up with Calculated column logic in report visual
Hi Sir, I have a calculated column as below: Its working fine in this table level. But when I use this column in a table visual in report its not working correctly and its giving 'Yes' in all rows of the table for same deviceid: FYR, screenshot attached: It should contain only rows where there are warnings(Yes). But when i remove the OpenMinutes,Storetime and Door Time from above table, I get the correct output: My expected output for filtered sitecode 73 and deviceid 2452 is i need below columns along with Store Time, Door Time and OpenMinutes(openstateduration table): Store Time comes from Openstateduration table DateTimeRounded which is linked to Door table DateTimeRounded(Both date columns should match): Door Time will be coming from Createdon date of Openstateduration table which is linked to Door table Createdon(Both date columns should match): Modeling for these tables are: Could you please help me fix this issue? Also, much appreciated if you help me with alternative or better solution like pushing Warning logic to power query or creating better modeling for this issue etc. PFA file here B&M (4).pbix Please feel free to let me know if any issues! Thanks in advance! SergeiBaklanCopy and paste/append values along with formatting within workbooks
Hi folks, We have a requirement where all the excel files from a particular document library in SharePoint and all the content in each sheet of each files (Source) need be populated in a specific sheet of different excel file (Target) along with all the formatting in source. Is there any way to automate this? Thanks in advance.Solver VBA doesn't apply all constraints
I have the following VBA code: SolverReset SolverOk SetCell:="$C$27", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$22:$C$24", _ Engine:=1, EngineDesc:="GRG Nonlinear" SolverAdd CellRef:="$C$24", Relation:=1, FormulaText:="($C$22+$C$23)*2" SolverAdd CellRef:="$C$30", Relation:=1, FormulaText:="3" SolverAdd CellRef:="$C$30", Relation:=3, FormulaText:="1" SolverAdd CellRef:="$C$22", Relation:=1, FormulaText:="12" SolverAdd CellRef:="$C$23", Relation:=1, FormulaText:="3" SolverAdd CellRef:="$C$24", Relation:=3, FormulaText:="0" SolverAdd CellRef:="$C$22", Relation:=4, FormulaText:="integer" SolverAdd CellRef:="$C$23", Relation:=4, FormulaText:="integer" SolverAdd CellRef:="$C$24", Relation:=4, FormulaText:="integer" SolverOk SetCell:="$C$27", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$22:$C$24", _ Engine:=1, EngineDesc:="GRG Nonlinear" SolverSolve userFinish:=True when I run it the solver doesn't apply the constraint C30 >= 1, and when I open up the solver dialog to see the constraints used it is missing from the solver. If the constraint is then added in the dialog it does get solved properly.
Auto-Populating Data From One Sheet To The Other
My structure is 2 separate tabs in the excel sheet. In one sheet, my "Devops Tasks" sheet, I have a status of "COMPLETE", and currently, everything besides 2 are set to "INCOMPLETE". When I set the status to "COMPLETE" my formula populates this data over here to my other sheet which is labeled as "Handover Report" within my "Completed DevOps Tasks This Shift": Right now though, it only populates as #NAME? - What exactly am I doing wrong here in this formula? =@IF(COUNTIF('DevOps Tasks'!C[6],"COMPLETE")>=4,INDEX('DevOps Tasks'!C[2],@AGGREGATE(15,6,@ROW('DevOps Tasks'!C[6])/(@'DevOps Tasks'!C[6]="COMPLETE"),4)),"") As a side note, but maybe not entirely relevant - one way I'm auto-populating is through what I do within JS: const completedTasksTable = handoverSheet.addTable({ name: 'CompletedTasks', ref: 'A' + (handoverSheet.rowCount + 1), columns: [ { name: 'ID', filterButton: true, width: 15 }, { name: 'Title', filterButton: false, width: 50 }, { name: 'Completed By', filterButton: true, width: 25 }, { name: 'Sprint', filterButton: true, width: 20 } ], rows: Array(10).fill().map((_, index) => [ { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!C:C,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!D:D,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!B:B,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!A:A,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` } ]) }); const completedTasksHeaderRow = handoverSheet.getRow(completedTasksTable.headerRow?.firstRow || +1); if (completedTasksHeaderRow) { completedTasksHeaderRow.font = { bold: true }; completedTasksHeaderRow.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF0F5FF' } }; } completedTasksRow.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF1E4D8C' } }; completedTasksRow.font = { color: { argb: 'FFFFFFFF' }, bold: true };Create a Power query or Dax to find Open Status more than 1 hour
Hi Team, I have the door table Here I want to find that whether door is being open continuously(having open status & not closed) for more than 1 hour for each DeviceId. Data Description: The Status column shows whether door is open or Closed. CreatedOn column shows time duration. DeviceId column shows Deviceid. My first requirement is: We need to add the minutes(createdon) between each row for same deviceid where the status = open. But where a status = closed (if closed comes in between open) we reset the number of minutes. So we sum by the previous value where status = Open - Something like If current status = open and previous status = open then sum the time and do the below: Sum(previous value + new sum) Where/if status = closed set time count = 0 Sum(previous value + new sum) is going to take into account only the consecutive open status >60 min for same divice id whenever status is closed, this Sum(previous value + new sum) will be reset to/equal to 0 So on the first open status, the count will be 0. Then the next open status it will be time between 2 open statuses, then on the third it will be the time between two open statuses add the previous time.but any close status then time is reset. Could you please help me with a power query logic/dax logic? Second Requirement: This is only a sample data. But we have billions of rows of data in our dataflow that comes from SQL views. So, thought not to use DAX for this calculation in future. we can precalculate the maximum open state duration by preprocessing data (SQL, Power Query(dataflow), Spark, anything else...). So can you help me prepare data upfront. we need to solve the problem with a with a proper data model. for example, create a table with the longest open events upfront (in whatever technique you are comfortable with) Could you please help me to achieve this? PFA file here B&M.pbix Thanks in advance!"OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELECTED
HELLO , I HAVE OFFICE 365 . FROM 1 MONTH AGO THE BUTTON IN DATA TAB THEN PROPERTIES AND IN IT "OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELCTED AUTOMATICALLY AND WHEN WE REFRESH DATA IT GET OVERLAPPED . EARLIER IT WAS WORKING FINE LIKE IN SCREEN SHOT WE HAD SELECTED "OVERWRITE........" TAB BUT WHEN WE PRESS OK AND REOPEN IT IT GET DESELECTED PLEASE HELP ON THISREMOVE A CHECKBOX FROM EXCEL WORKSHEET
I was handed an Excel worksheet that has three checkboxes that I wish to remove but cannot seem to remove them. Excel Help says to right click the Box and then hit Delete. There is no Delete in the ensuing dropdown menu and Delete on the keyboard does not work. Any suggestions?
