|
kumba posted:my first thought: your last argument is <=DATE(2021,9,30), but that means MIDNIGHT 9/30/21. Are there times hidden in column K, and are there entries for 9/30/21 that sum up to your difference (and a corresponding lack of entries on the final days of other months that do calculate right)? 746,307-688,249=58058 Goddamn, got it in one. Thank you! The months that were correct had a last day of the month fall on a holiday or weekend. If I drop the DATE() function and replace it with a cell reference to a cell with a date in it, does it do the same thing? As in, should I set my start cell as 9/1/2021 and my end cell as 10/1/2021?
|
# ? Jan 25, 2022 19:54 |
|
|
# ? May 25, 2024 15:00 |
|
LawfulWaffle posted:
yes and yes - and drop the = from the <= to avoid an exact 10/1 at midnight entry being counted in september
|
# ? Jan 25, 2022 20:21 |
|
kumba posted:yes and yes - and drop the = from the <= to avoid an exact 10/1 at midnight entry being counted in september Thank you kindly. I don't think a midnight entry is likely but thank you for pointing out that wrinkle with the >= v >, gonna tuck that into the ol' mental rolodex,
|
# ? Jan 27, 2022 06:25 |
|
I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns. Take this hypothetical example with hypothetical data (color-coded for clarity): In this example, the spreadsheet is combining the law enforcement agency and the crime into a single column. I want to be able to separate the agency and the crime into two separate columns, and have the spreadsheet look like this: Is there any way to do this using a formula or some kind (any kind ) of shortcut, so I don't have to do it by hand? The actual spreadsheet I'm working with is really big (imagine 100 agencies, 20 crimes each, for 15 years) and ain't nobody got time for that.
|
# ? Feb 2, 2022 20:11 |
|
Rabbit Hill posted:I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns. There might be a way to use sumifs or index/match, but an array formula is the easiest way for me. I'll call the first chart "ugly" and the second one "neat" Step 1: Add "helper" rows to the top or bottom of the ugly table. For example, if you insert rows above, column B would have "Agency #1" in B1 and "Murder" in B2. This should match the labels you use in the neat table. Step 2: Build your neat table. Step 3: Create a formula to pull the numbers based on each column as your criteria. If for example, the "Year" label of the ugly chart started in A3, with your helper labels above in rows 1 and 2, your formula for the first row would be something like: =sum(if((Neat!A2=Ugly!$A$4:$A$6)*(Neat!B2=Ugly!$B$2:$J$2)*(Neat!C2=Ugly!$B$3:$J$3), $B$4:J$6, 0)) Step 4: Press Ctrl+Shift+Enter to turn that formula into an array formula Step 5: Copy that formula down to the other columns The formula in step 3 basically applies all 3 criteria to the ugly chart to find which numbers to pull. The multiplication is just a way to do an "AND" function, and the fact that it sums doesn't matter since it's only pulling a single number for each.
|
# ? Feb 2, 2022 20:42 |
|
Rabbit Hill posted:I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns. That can be done with an unpivot and then column split in the Power Query editor if you are willing to lose the cell/color formatting. There are other benefits to this method like being able to go back and edit the steps but I won't preach my Business Intelligence Bull poo poo here. You may have to download the PQ add-on from Microsoft if you don't have it installed. 1. Select the entire range of data then go to the Power Query tab and click 'From Table/Range', it should open the PQ editor. Open the Transform tab, click the Year column header, then click the dropdown next to Unpivot Columns and click Unpivot Other Columns. You should then have 3 columns titled Year, Attribute, and Value which correspond to your Year, Agency Crime, and Count columns. 2. Click the Attribute column, click the Extract button, then the Text After Delimiter dropdown option. Enter the hyphen as the delimiter and click OK, it should split that Attribute column to Attribute.1 and Attribute.2 columns. You may have to play around with this or other split/extract functions depending on the data you actually have. I can imagine your agencies may not all conform to a single rule. 3. Then you can rename all of the columns as you want - just double click on the column header. 4. When you're done, go back to the Home tab and click Close & Load To..., then select where you want to put the new table - probably a new worksheet. Click Load and you're done!
|
# ? Feb 2, 2022 21:07 |
|
Rabbit Hill posted:I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns. Power Query is absolutely the way to go about this, as others have mentioned. If you haven't done it before, it's under Data. You can create a new file and Get From Other Source, then point it at the original file. Another way is to make your data into a table by highlighting and Ctrl + T (or Insert -> Table), then using Data -> Get Data From Table The interface is pretty intuitive even if the process might not be, but you can fiddle with the steps and it will process the entire file how you tell it.
|
# ? Feb 3, 2022 18:40 |
|
I've got a Power Query question! I'm using it to import a list of receipts with dates attached so that I can create PivotTables from it. But in order for summarizing by date and cross-referencing with other tables to work properly, I need to fill in all the dates that are missing (i.e. even if there aren't any receipts for the last week of January I still need all those days in my PivotTable). My current solution is that after I update my query, I scroll to the bottom of the table and type the first day of the month and drag it down until I have every day in that column (the rest of the columns can be blank, it's fine). Is there an easy way to make Power Query fill in those missing dates on its own? It feels like the hard part would be getting it to recognize that I specifically want to fill out the month, and not just fill in the dates between the ones I already have.
|
# ? Feb 10, 2022 16:55 |
|
Haven't had my caffeine yet this morning but that sounds like a job for a union/merge with a date table. Create another table that has all the dates for the month and add that to PQ, then do a merge from that table to your receipts table using the date fields and a Full Outer join. This will match & merge all of the dates from both tables, any dates with blank receipts will still show up. If it were me, I'd create a date table with the next year+ of dates that also has columns for fiscal year, fiscal month, and fiscal week. Then when you do your monthly analysis, just add a step before the merge mentioned above and filter the date table to month you're working on.
|
# ? Feb 10, 2022 17:15 |
|
If you're keen to fully automate this, the List.Dates function will probably be quite helpful. You'd have to do some of the maths yourself to figure out the count of dates you need to generate, given your first and last dates in the given data, plus a bit to get to the end of the final month. Edit: Simpler still, apply Date.EndOfMonth to your max date and the hard part of the calculation is done. Heavy_D fucked around with this message at 10:19 on Feb 11, 2022 |
# ? Feb 11, 2022 10:16 |
|
I don't know if this is beyond the scope of Excel, but I thought I'd at least ask before giving up. The software we are currently using for managing our inventory and known products is going to be replaced by a different one that lacks the same search capabilities (yay for parent company shenanigans). I've been trying to find a way to use Excel instead for searching, and it sort of works, but it's unsurprisingly much more fiddly than the current software we use. Below is an example of how it looks in Excel with only the bare minimum required fields: Apart from the first 3 columns plus the MRSP column, all of the columns contain a mix of text and numbers somewhere. While I can search by using the filter buttons on each column, this is as mentioned fiddly and I would like to emulate the current software by simply being able to type what I'm looking for in a cell in row 2 and have it search the whole column beneath it and filter out the results. Likewise clearing the contents of the cell should also clear the filtering. Because we don't always know the exact measurements of what we're looking for, we often do a range search between two values, which I can replicate by hitting the filter button and going into "Number Filters--> Between", but again, a lot of clicking for what is really quick and easy today; we simply type something like 80:85 in a cell to list everything from 80.00 to 85.00. In the text fields, we mostly use wildcard searches with * . I'm thinking that it should be possible, but I imagine it will require VBA which I don't know. I've been googling for the past week or so trying to find a ready made solution for this, but so far I've come up empty. Normally I'd resign myself to my fate, but our inventory list has some 65.000 entries which often requires a ton of fine-tuning searches across multiple columns to find what we're looking for, hence why I'm desperate for something faster and easier than Excels filter button fiddling. Is what I want even possible in Excel? AG3 fucked around with this message at 14:37 on Feb 15, 2022 |
# ? Feb 15, 2022 14:34 |
|
Yes possible but I think you’d need to use VBA to get what you want (although it’s 2am and I’ve been helping with database migrations all night so I might be overlooking something). I know you said you don’t use vba but I’m not in front of a computer so I’m just going to give broad strokes of how I would approach it. I think I could put it together pretty quickly but I have spent so many years being the Excel Guy. I’d use the worksheet On Change property: *Target range of 2a:2j (rows you have data in) *If change not in target range then exit *If there’s an existing filter on this column, clear it (I think you need to otherwise might have issues but might not) *Then you’d work out what type of filter you need to do and the values with some string functions: - If string contains a colon you’re using the between filter (if() and instr() function) - If you’re using the between filter you separate the lower and upper (from and to) values to variables (left() and right() functions) - Otherwise (I guess) you’re doing a wildcard search and not exact? - In that case use the contains filter (or equals if you do want it to do exact) *Apply the chosen filter to the column (I think it’s like target.column to aim it at the right column) It’s definitely going to be a bit of dicking around to build this yourself if you’ve not dealt with excel vba before. What you can do to get started is hit macro record button and then record a macro while you filter a column with a contains filter, the clear the filter, then do a between filter. That will give you some of the pieces you need to put together for it. Then you’d need to copy and modify those recorded statements from specifics like Range(“a:a”).filter.contains.value=”80:85” To variables like Range(target.column).filter.whatever.value=lower_value&”:”&upper_value (These aren’t real code, just stupid examples) One you’re going to have to google is something like “excel vba onchange target macro”. That should give you the where and how to setup the code that will only fire when the values change and how to target it to just the area you want to watch for changes in. That way if you make a note or add a formula it doesn’t bother you.
|
# ? Feb 15, 2022 16:34 |
|
Here you go, I knocked this up quickly and think it will do what you need. Stick it in the worksheet code that you’re working on. It’s a bit messy cause it’s been through email and then phone posting and it’s not perfect, I had to add in the last “show row 2” line because when excel filters it filters everything in the range, so when I typed “1:20” it would hide row 2 because it is not a number between 1 and 20. !Change a2:d2 to the range where you will be entering filter values. code:
|
# ? Feb 16, 2022 00:21 |
|
Thanks a lot for the code! I couldn't quite get it to work, but at least I learned something trying to troubleshoot. Basically when I type something in a box, it'll pop up a runtime error 1004 "Autofilter method of range class failed", with the debug button highlighting this: code:
code:
The same error likewise pops up when I try to clear a search box, but with the debug marking that relevant part of the code. Googling seems to indicate that it's a common error, but I can't quite wrap my head around how a fix would work for this particular code. Time to call it a day and look at it again tomorrow when brain is less fried.
|
# ? Feb 16, 2022 15:29 |
|
Unfortunately Error 1004 is a surprisingly common error that doesn’t tell ever tell you much detail. I’m taking a guess but I think the data_range might be the issue. If you run it again, you can jump into debug and have a look at what value that has. Once you’re in the code view, click on View menu at the top of the screen and choose Locals Window (if you don’t already have Locals showing). This will list all the variables/expressions and their current values. It should be “a1:$P$30” style - where P30 is the last cell with data in it that you’re interested in. Useless extraneous information: you could also do this with the Immediate window. If you turn Immediate on from the View menu, then you can type “print data_range” and it will tell you the current value of the variable. If for some reason it’s set to something stupid like $XFD$1048576 then you need to tell excel it’s being stupid. It sometimes picks up where data once was or where formatting runs to the end of the sheet just because… You should be able to reset that but it’s sometimes a fight. I usually select from the right of my data to the end of the sheet and then ‘clear contents’ and ‘delete’. Doing both seems redundant but that’s my experience with excel. Then repeat the same at the bottom of the data. If that doesn’t resolve things you can try adding the line “Activesheet.Usedrange” at the top of the code, this usually(but not always) forces excel to re-evaluate the actual range with data in it. Finally, if its still having the same issue (saying you have data out to a stupid range where you don’t and refuses to to be fixed: Copy the data to a new worksheet. Copy the code to the new worksheet. Use that worksheet. —- One other thing to check: make sure you’ve updated the “A2:D2” in the first line of the code. The D should be the last column you have data in.
|
# ? Feb 17, 2022 02:49 |
|
After changing thiscode:
code:
I have the code pasted in the sheet directly instead of as a separate module since I couldn't get it to run at all when it was in a separate module, I don't suppose that's an issue?
|
# ? Feb 17, 2022 10:19 |
|
Nah all good in the sheet - that’s what it’s intended for. Ditch the “last_cell” and then you’ve manually set the range it’s looking at. Not great long term but will help with testing. code:
Let me know if that sorts it, otherwise I can throw up an example file tomorrow and you can start from that / see if that works for you. I don’t think any of that code is excel version specific so I don’t think it’s that - it’s sometimes a problem with this sort of thing, like if you use .formula2 instead of .formula it won’t run in excel 2016 but will in later versions.
|
# ? Feb 17, 2022 11:03 |
|
Holy crap, that actually fixed it! It doesn't seem to like "& last_cell" , but manually setting the range and dropping last_cell made the whole thing work! I guess the code needs to change as the list grows, but that's a tiny price to pay for a an actual practical filter function. One thing I noticed is that the wildcard "*" are reduntant since the filter in Excel seems to do that by default; i.e if I search for 1234 it'll still show a line containing ABC1234EDF whether the wildcard * is there or not. Is there a way to make it default to "begins with" when there are no wildcard * present? And for the numbers only fields (columns A to C) "equals" when there are no ":" present?
|
# ? Feb 17, 2022 13:40 |
|
Why not set last_cell to be the actual cell and then use last_cell.address ? Phone posting so I haven't tried the code, but I'd expect that the issue comes from assigning a cell address directly to a variable which might not result in the expected type of string... Edit: After quick test the above doesn't seem to be the case, cell.address seems to return a nice boring string in A1 format with dollars. Shazback fucked around with this message at 16:51 on Feb 17, 2022 |
# ? Feb 17, 2022 13:58 |
|
I'm using SUMIFS to sum a column based on two different categories, and I am trying to show the breakout for pretty much every permutation (there are a lot). So I used: =SUMIFS($H$2:$H$207,$C$2:$C$207,INDIRECT("B214"),$E$2:$E$207,INDIRECT("A214")) B214 is basically a text category that reference something in the C column, and same for A214 and the E column. This formula works, and pulls the numbers I want. However I want to be able to drag to it down and B214 and A214 update to B215 and A215 next, etc. I believe the INDIRECT is what is preventing this. Is there something else I can put in there? If need be I can manually change all those cells but would prefer not to. I tried just putting quotes there as well. I am not sure that indirect is the best thing here, but when I was searching online for help that is what people seemed to be using, but they were mainly referencing sheet names. Edit: I am not sure this is best way but I fixed by adding a concatenate and row reference: =SUMIFS($H$2:$H$207,$C$2:$C$207,INDIRECT(CONCATENATE("B",ROW(B214))),$E$2:$E$207,INDIRECT(CONCATENATE("A",ROWA214)))) It now sums and works when I drag it down. I am glad this works, but is this the best way to do it? Fritzler fucked around with this message at 18:46 on Feb 17, 2022 |
# ? Feb 17, 2022 18:09 |
|
AG3 posted:Holy crap, that actually fixed it! It doesn't seem to like "& last_cell" , but manually setting the range and dropping last_cell made the whole thing work! I guess the code needs to change as the list grows, but that's a tiny price to pay for a an actual practical filter function. Yeah that’s all possible but it was just easier to knock it up than to try type out each change. The wildcard filtering doesn’t sound like it’s doing what it should from your description, so hopefully this works. I changed how the data range is determined, since the previous attempt wasn’t happening. I think P is your last column but that may need updating - I added capslock notes for each of them. code:
|
# ? Feb 17, 2022 23:51 |
|
Fritzler posted:I'm using SUMIFS to sum a column based on two different categories, and I am trying to show the breakout for pretty much every permutation (there are a lot). If A214 and B214 are just text you shouldn’t need the Indirect. This way when you drag the formula down it will update those values automatically. =SUMIFS($H$2:$H$207,$C$2:$C$207,B214,$E$2:$E$207,A214) I usually use Indirect for things like named references, variable stuff. Exceljet has some good examples of when it’s useful.
|
# ? Feb 18, 2022 00:08 |
|
DRINK ME posted:Yeah that’s all possible but it was just easier to knock it up than to try type out each change. The wildcard filtering doesn’t sound like it’s doing what it should from your description, so hopefully this works. This works more or less exactly how I had imagined. Thank you so very much, this is going to save me no small amount of headaches if my worst fears regarding the new software comes to pass! If I can buy you a couple of beers through paypal or something do let me know.
|
# ? Feb 18, 2022 11:51 |
|
Thanks but not necessary. It’s an interesting idea and I think I might be able to use something very similar at work. I’ve always just accepted “this is how to filter excel” without considering it could be done different or better.
|
# ? Feb 19, 2022 01:15 |
|
So I've been fiddling with this for a bit and tweaked a few things (thanks to Google; I still can't code the stuff ) and there is one thing that has me stumped again. How on earth do you make sorting results not include row 2, the one you enter search criteria into? You'd think there would be an easy way to tell Excel "exclude this row from sorting" or "anchor this row in place and don't move it or its contents" but there doesn't really seem to be, except for row 1 which is the header. The problem of course is that as soon as you sort, the results start at row 2, which means that you can't use the search box again unless you delete the contents that just appeared (bad) or try to CTRL+Z, which works... sometimes at least. For now I'm resetting it by giving the search fields in row 2 a blue background, which I can sort by to get it to come back on top after I'm done with the actual search. If getting the sort function to leave row 2 alone is impossible then the only solution that comes to mind is to replace entering data in row 2 with ActiveX text fields for entering data instead or something. But that seems like a lot of work for something you'd think was straight forward to tell Excel to do (leave row 2 alone). But then again a lot of things aren't straight forward in Excel, so.
|
# ? Feb 21, 2022 15:42 |
|
That’s a good question. I can’t think of a way to do it off the top of my head. I think you’d have to either build your own sorting or move your filters outside of the table (maybe above the header row) so that the normal sort can do it’s job. I’m away from computers today so can’t see how sorts work (and I can’t remember the last time I used sort in vba) but maybe hit record macro, sort a column, stop recording, undo the sort and then you can take a look at the code it used to sort the data. If it ends up being something simple like range(“A1:P100”).sort column:=3, sortorder:=ascending then you could possibly just push it to “A3:P100” instead. Although I doubt it’s going to be that easy. I’ll let it roll around in the back of my brain and have a look tomorrow when I’m back at work. Sometimes that works for me; it’ll be like midnight and my brain will go “hey I know how to solve that stupid work problem now” and I get to experience disappointment and excitement at the same time.
|
# ? Feb 21, 2022 22:50 |
|
Let's say that in A1 I have "February 14 9:00:00" and in B1 I have "February 15 9:00:00". Then in C1 it just has "February 15" with no time attached. Is there an easy way to determine how many of the hours between A1 and B1 are in the date given in C1? The end goal would be a formula that in this case would spit out '9.00'. I feel like the answer will be "I can do it, but it will involve a lot of suffering and helper columns and nested formulas", but hope springs eternal.
|
# ? Feb 28, 2022 19:10 |
|
Everett False posted:Let's say that in A1 I have "February 14 9:00:00" and in B1 I have "February 15 9:00:00". Then in C1 it just has "February 15" with no time attached. Is there an easy way to determine how many of the hours between A1 and B1 are in the date given in C1? The end goal would be a formula that in this case would spit out '9.00'. a simple =ABS((C1-B1)*24) (or really just a =(B1-C1)*24 but going in that direction feels wrong to me for some reason) in this case will give you the 9 hours but i'm guessing real data is going to be more complicated than that
|
# ? Feb 28, 2022 19:16 |
|
Oh that's way simpler than what I was thinking, I might be able to make that work in conjunction with a bizarre series of IF statements. That's exactly the nudge I needed, I've been staring at it so long I've overcomplicated everything in my head. I'm trying to do something clever with timesheets for a department whose current timesheets are done in excel and are. An absolute nightmare. Absolutely everything gets calc'd out manually and usually wrong. I briefly thought I had managed to automate all of the obtuse rules around pay calculations before I remembered that the 24 hour shifts don't actually line up to midnight-midnight which is how additional holiday pay is determined... So if someone has a 24 hour shift from 9-9 on the 20th I need to correctly allocate 9 of those hours to holiday, but if they work 9-9 on the 21st I need to make sure that 9 of those hours aren't included. Which I think I can do with simple subtraction like you have, I just have to determine what happens based on whether the date the shift starts or ends corresponds to one of the dates in my table of holidays.
|
# ? Feb 28, 2022 20:57 |
|
Just chiming in - the reason that works is Excel dates all include a time component, even if it’s not visible. So where you have 15th Feb 2022 Excel actually sees that as 00:00 15/02/2022 (or 02/15/2022 if you use American format). Then under the covers Excel stores it as 44607, which is the number of days since 01/01/1900. With the time component it is 44607.00 - the .00 represents the fraction of the day, or 24 hours. So jumping back to the 9:00am example it would be 44607.375, where .375*24=9. 9:00pm would be 44607.875, where .875*24=21. 21 hours or .875 of the day, if you’re used to 24hour clocks then it’s a simple 2100hours. Hopefully you can manage to avoid having like an extra sheet of helper columns and formulas but just going to throw in one useful one for dealing with time =number - trunc(number) This pulls just the decimal part of the number, or when working with dates and times it’s just the time. Personally I hate working with times and dates because I wish it was decimal - French revolution time or even swatch @beat time would be better.
|
# ? Feb 28, 2022 23:02 |
|
Speaking of SUMIF functions and time, my wife is working up a simple expense sheet for her startup to help her break down costs by type and month- The SUMIF for breaking down costs based on type is easy enough, but I'm not sure how best to do the by month SUMIF. Is there something I have to add to the second argument of the function to help it recognize months in Column B, or do I need to write out the months in Column I in a certain way? C-Euro fucked around with this message at 05:31 on Mar 2, 2022 |
# ? Mar 2, 2022 05:28 |
|
One way to do it (and there are multiple like with everything in Excel) is change your Jan-Mar in column I to dates: 01/01/2022 02/01/2022 03/01/2022 If you still want them to look the same you can go to Format Cells | Custom and use dddd. Then your formula for J2 would be =Sumifs(c:c, $b:$b, “>=“&i2, $b:$b, “<“&i3) That is summing C where B is between those dates. The only catch is you need an extra value in i5 - 04/01/2022 would make sense. You can hide it if you want to keep things tidy but the formula just needs an end date. Edit: you could also make little pivot tables instead and save yourself the formula bother. The pívot table can automatically group at Month/Quarter/Year and would make expanding it as the year continues a cinch. DRINK ME fucked around with this message at 06:25 on Mar 2, 2022 |
# ? Mar 2, 2022 05:54 |
|
C-Euro posted:Speaking of SUMIF functions and time, my wife is working up a simple expense sheet for her startup to help her break down costs by type and month- Put this in J2 and copy it down. quote:=SUMIFS($A2:$A, $B2:B, ">=" & DATEVALUE(MONTH($I2&1) & "/1/2022"), $B2:$B, "<=" & DATEVALUE(MONTH($I2&1) & "/31/2022")) quote:=MONTH("January" & 1) converts to 1 Edit: apparently the above formula does not like 2/31/2022 kinda dates, DATEVALUE is not necessary, and you need to use $A$2/$B$2 to anchor those ranges so the correct way apparently is this: quote:=SUMIFS($A$2:$A, $B$2:B, ">=" & MONTH($I2&1) & "/1/2022", $B$2:$B, "<=" & EOMONTH(DATEVALUE(MONTH($I2&1) & "/1/2022"), 0)) The last part generates the date for the end of the month Strong Sauce fucked around with this message at 09:24 on Mar 2, 2022 |
# ? Mar 2, 2022 09:09 |
|
Can index and match be conditional? Like lets say I have some arbitray number of rows and two columns A and B. Can I then add a third column and using index and match have column C equal to some concatination of every row where B is equal to the A on the specific row I'm working on? code:
code:
|
# ? Mar 3, 2022 04:12 |
|
If you just want to check for an item in a cell [A1] that there is at least one match in a column [B] : = Countifs( B:B , A1 ) > 0 This formula can be extended down (to replace A1 by A2 in the calculation in the next line) or expanded (if you have other criteria to determine if a row should be true or false), and if you want to match an exact number of times you can just change the equivalence at the end (exactly one match being " = 1 " as you'd expect). Is this what you were looking for?
|
# ? Mar 3, 2022 08:32 |
|
DRINK ME posted:Just chiming in - the reason that works is Excel dates all include a time component, even if it’s not visible. So where you have 15th Feb 2022 Excel actually sees that as 00:00 15/02/2022 (or 02/15/2022 if you use American format). Then under the covers Excel stores it as 44607, which is the number of days since 01/01/1900. With the time component it is 44607.00 - the .00 represents the fraction of the day, or 24 hours. Yeah, that's why I'm now running into the problem with matching to my list of holidays where Excel goes, "Oh, the shift ends at February 21st at 9:00 am? That's not February 21st at midnight! No holidays detected." Somehow this was not the part that I thought would give me problems.
|
# ? Mar 3, 2022 16:05 |
|
I don't know that this is the best thread for it, but since Excel is so often used for working with csv files...is there a better tool? Or a specific set of options I can turn on to have Excel not do any of its allegedly helpful things with say, date and time stamps? I'm talking 500,000+ rows of data, so a notepad++/general IDE is out because I need the cell-based display, but good lord Excel really needs a very basic no-frills raw csv mode or something.
|
# ? Mar 3, 2022 17:04 |
|
Before pasting CSV data or otherwise importing, select the entire sheet and change the number formatting to Text?
|
# ? Mar 3, 2022 17:26 |
|
Wandering Orange posted:Before pasting CSV data or otherwise importing, select the entire sheet and change the number formatting to Text? And then when I re-open the file to work with it, I have to convert it all to text again else it saves dates in whatever hosed up format it's decided is best. I suppose I could macrofy it, but then I have to have a workbook with my macros in it open when I'm working with the files too...It's absolutely infuriating. I'm close to giving up on Excel entirely and seeing if Google Sheets handles them any better - I've tried about everything I can think of with Excel (hence turning to my fellow goons for advice)
|
# ? Mar 3, 2022 17:29 |
|
|
# ? May 25, 2024 15:00 |
|
Lib and let die posted:And then when I re-open the file to work with it, I have to convert it all to text again else it saves dates in whatever hosed up format it's decided is best. I suppose I could macrofy it, but then I have to have a workbook with my macros in it open when I'm working with the files too...It's absolutely infuriating. I'm close to giving up on Excel entirely and seeing if Google Sheets handles them any better - I've tried about everything I can think of with Excel (hence turning to my fellow goons for advice) Turn off automformating? Past values only?
|
# ? Mar 3, 2022 17:31 |