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.
Conditional data validation drop downs
I have a table with everyone's availability. Column A is names, Column B is Monday morning, Column C is Monday afternoon and so on. In another cell, I want a drop down list of names if they're available at that time. If( [Monday Morning] = "Available") put it in the drop down list and then do that for the whole week. When I try to do formulas in the data validation page it doesn't work.
Issues With PowerQuery Using 2 Tables with different Data Sets
Im having issues using power query when I try to use 2 tables to make a pivot table. I am using a sheet that pulls information from a system I use for maintenance. The first screenshot is a subset that breaks down sub items for a main "work order". The next 2 screenshots are from a separate table that show the main "work order" and has an "Equipment Pool" that shows who owns the vehicle in question. When trying to create a pivot table, a lot of information shows as blank. (see bottom screenshot) I am wondering what I am doing wrong for it not to be able to pull the "Equipment Pool" when it aligns to the "Asset ID". Any help is appreciated. (For security reasons I have not put all information from the tables, only the relevant ones)Data Pulling
I'm trying to figure out the best way to pull data together from multiple tabs within an excel file. The file is made so that it can track issues within reports that are discovered. I'll try to break it down the best I can to explain what I have and what I'm trying to do. Within the file there are 5 tabs that are considered locations and they are named: LX, LV, CR, HR, FL Each tab is set up the same way with the only real important information in these columns of a table Column A - PCR Date Column C - PCR Owner Column G, I, K, M - Modification Category (1-4) these fields are actually drop down options from the Category Master List Within the file there is a 6th tab that has at able in it that is called Category Master List. Column A - Options for the dropdown in the above tab Modification Category columns Column B - Breakdown into one of three main categories (Billing, Compliance, Quality) Now this is what I'm trying to do: Fiscal Year Data (September 2025-August 2026) - I need to see how many times each of the dropdown options within the Master List show up in column G, I, K, M for each of the 5 location tabs. Monthly Data - I need to see how many times each of the dropdown options within the Master List show up in column G, I, K, M for each of the 5 location tabs along with how many times there is an entry with a date within that month. I would like to try and figure out how to put everything into one data table/chart type thing so it's easier to see and compare. Currently, everything needed is across a few different pivot tables and I would really like the data to pull itself automatically instead of me having to go in and copy all the data from one spot into another just to get the pivot results. Below is what I'm having to look at currently.
How to write a script or any PQ or in Excel to download the zip files from a Webpage
Dear Experts, Greetings! https://www.etsi.org/deliver/etsi_ts/138300_138399/138306/ Could you please help me on how to download the pdf.zip files from above for all the versions? Using a single command in Excel or PQ-option. Thanks in Advance, Br, AnupamFormula to retrieve data from several sheets and return values from given criteria.
I am trying to get data from each of our areas of the school staff timetables EY MY LY and summaries what days each staff member works where to another sheet, as staff move around. I would appreciate some help please. This is how far I got: =IFERROR(VLOOKUP(A10,EY!$C$3:$G$33, 2, FALSE), IFERROR(VLOOKUP(A10,MY!$C$3:$G$33, 2, FALSE), "Not Found")) This just returns; I tried to add in the same formula for LY but it is saying too many arguments, this is why Michelle is Not Found. Will I also be able to return the area they are in on each of the days ? Any assistance would be very much appreciated.
Please update Excel to handle more than 15 digit numbers!
I'm a professional database designer, and this limitation has caused me no end of headaches. The problem: numbers that are more than 15 digits long have all digits after the 15th converted into zeros. Microsoft provided workaround: format the the field as text. This workaround is only useful if you are doing data entry directly into an existing Excel spreadsheet that you are able to format the cells ahead of time. It's incredibly unhelpful when you are pushing and pulling data from different data sources. For example, if I need to push data out of a program like FileMaker to Excel, and the system I am pushing from has numeric fields (in particular ID fields), excel sees them as numbers and replaces the digits after the 15th. This jacks up formulas as well as any data synching possibilities. In situations like that, I end up having to export the data as .csv file, and then pull it in to an excel sheet and then do a bunch of conversion options on import (which does not always work, btw). But this is not a practical solution for every day users who are not tech savvy. I should be able to export the data directly to excel without the data being converted. This has been a big problem for every client I have that requires data being pushed to Excel. And this problem has existed for decades. Other spreadsheet programs (like google sheets) do not appear to have this issue. Can someone please explain why Excel continues to cling to this archaic standard? Are there any plans to update Excel to handle more than 15 digits? I know I am not the only one who has run into this problem. I've seen all kinds of posts about it. But trying to let Microsoft know how much of a problem this is has been a challenge. Their article on the subject had a link to give product feedback, and that link led me to this space. So here it is: product feedback for Excel. PLEASE FIX THIS! Thank you. ChrisTOCOL/TOROW Treat Thunks as Errors
I just reported a bug to Excel in which the TOROW and TOCOL functions, if asked to delete errors, will also delete valid thunks. ISERROR returns FALSE when presented with a thunk, so, arguably, TOCOL shouldn't treat it as an error either. I'm running Office 365 on Windows 11 on a Samsung Yoga laptop. Steps to reproduce: Paste this into a single cell in a spreadsheet: =LET(th, VSTACK(LAMBDA(4)), (@TOCOL(th,2))()) Expected behavior: should return 4. (Delete the ",2" and it does exactly that.) Actual behavior: #CALC error Impact: Breaks workflows for SCAN, MAP, BYCOL, and BYROW Workaround: Use FILTER and ISERROR Details: An Excel "thunk" is a degenerate LAMBDA with no parameters. So if we used LET to make f equal to LAMBDA(4), then f() would always return 4. If a thunk is the final result of a function, it generates a #CALC error, but it's fine for intermediate results. This seems useless, but it's the only way to get functions like SCAN, MAP, and BYROW/COL to return anything but a scalar. For example, I have a piece of code where I need to repeatedly square a large matrix and save the values for further processing. If I get a zero value, I can save a lot of processing by "aborting" the operation. Since you can't abort a SCAN, I just return #NA. Then I'd like to use TOCOL(result, 2) to strip off the unnecessary values. But TOCOL discards everything. I can work around this by using a combination of ISERROR and FILTER, but I shouldn't have to.
