Date format with Text formula
Our take
Hi all,
Only semi-relevant: I created a template for my bookkeeper to use for our business credit card, to pull out the card holder names for each transaction. I have two tabs... one where she copies-and-pastes the transactions that come from the bank website, and one where she copies-and-pastes exported data from our accounting software. The accounting software report shows all transactions that are missing receipts, but no card holder names. She uses the credit card report to add card holder names to those. I added a helper column on each to pull out the date and amount, so the info on each sheet can be matched up.
My issue is on the tab where the credit card transactions are pasted. The helper column (column A) formula is:
=IF(E2<>"",TEXT(C2,"mm/dd/yyyy")&E2,TEXT(C2,"mm/dd/yyyy")&F2)
I had to include the text formula when referencing the date column because random cells were pasting in the numerical date format - inconsistent with the other lines. See row 43 on in the image below. All of the data was copied-and-pasted from the same report, at the same time. All cells are in the same number format (General). It's the same result for these cells if I just do =TEXT(cell,"mm/dd/yyyy") to pull the date. Why wouldn't the text formula result in the mm/dd/yyyy format for the numerical dates - and why would these random lines be different when copied and pasted all at once from the same .csv source?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Dates incorrectly pasting from financial statementsI'm attempting to use a new excel budget spreadsheet for tracking my expenses (linked here). In the sheet labelled "expense data," you are supposed to be able to copy/paste the transactions from your .csv bank statement with dates and other info. The problem I'm running into is that the dates don't seem to be registering as dates, and they are all flagged with a "data validation error." Even though the spreadsheet title says to paste them as MM-DD-YYYY, the only way it will accept the dates is as YYYY-MM-DD, or if I type the full date (e.g., March 6, 2026) it will automatically put it in YYYY-MM-DD format. So far, I've tried every single option from the "Paste Special" options and none of them paste the date in a format that is recognized as a date. I'm wondering if there is an easier way to help excel recognize these dates other than manually re-typing each one. For context, I am using Excel on a Mac. My dad uses the same spreadsheet (and transfers info from the same bank) on a Windows computer and doesn't run into the same issue. Thanks in advance :) https://preview.redd.it/lq0wh77757tg1.png?width=422&format=png&auto=webp&s=e39f3190ad71a4d369d2bf7350dfa494df74d0bf submitted by /u/120muffins [link] [comments]
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- Excel seems to think cell isn't big enough so 2 digit number looks crazy, no adjustment fixes issueI have multiple sheets that add together to make a total on the first page. Easy. The numbers are small - 3, 5, 10, nothing big. On the totals sheet, all the two digit sums are wrapped so that they are very small. I do not have wrapped text on but when I DO wrap the text, it stops wrapping them BUT there's this giant space between the two digits like "1 0" for 10. I tried everything I could think of - rewriting the formula as a SUM function (rather than this sheet + this sheet + this sheet), making sure each cell is formatted as a number, running "text to column" on each column of data, switching the font, switching the font size, changing the column size manually, double clicking on the columns to have them auto size, turning off shrink to fit, turning on shrink to fit again after, playing with the alignment.... Does anyone know what is going on and how I can solve it? Adding images (w/ some edits for privacy for my company). Will include the formulas (both versions). https://preview.redd.it/6n05pqeer4og1.png?width=1654&format=png&auto=webp&s=64729e1558bfba595d7d043b18d777b3e33e4ecf https://preview.redd.it/ydydstbfr4og1.png?width=1664&format=png&auto=webp&s=27e3d7cd18c2c380a5cf1a48853060a4bcd17c7c https://preview.redd.it/7dcraf9gr4og1.png?width=1106&format=png&auto=webp&s=4c93c635fbc20762a18cc8e137b6a40ee37c470e submitted by /u/catincombatboots [link] [comments]
- Pulling numbers from Text, then arranging as dateHey y'all.. I am hoping that someone smarter than I, can assist with this.. I've tried several ways (text to columns, TEXTJOIN) unsuccessfully. I run Excel for Mac (Version 16.107 (26030819)). It'd be great if the solution was dynamic, as I am continually adding to the Sheet. I trade options (specifically SPX), and my brokerage provides me with .csv's containing my transactions. I'm creating a workbook that tracks my trades. Column E (formatting is General) is the "symbol", which has the following syntax: -SPXW251010P6200 or -SPX251017P6150 (notice missing "W" in second example) What I am trying to accomplish is to pull out the expiration date - "251010" and "251017" in the examples above, which are YYMMDD - for each record, and placing it in its own cell as DD/MM/YY (formatted as Date). Any suggestions?? TIA submitted by /u/Interestingly_Quiet [link] [comments]