{ "media_type": "text", "post_content": "How to change a date field from 2002/01/15 to YYYY-MM-DD. The right click and date selection is not converting it for some reason. Even tried to do a custom field but it’s not changing :(", "post_id": "5ebf17655a899c002b23b10d", "reply_count": 22, "vote_count": 3, "bowl_id": "5a56f68d15f6ef0013aad256", "bowl_name": "Excel Genius", "feed_type": "bowl" }
null

How to change a date field from 2002/01/15 to YYYY-MM-DD. The right click and date selection is not converting it for some reason. Even tried to do a custom field but it’s not changing :(

like
Posting as :
works at
You are currently posting as works at

a long way you could do it is concatenate the cell and espérate year month and day and then out it together using a formula in the form you’d like. idk how to do it easier!

likefunny

 separate*

It's in the "More Number Formats" selection box, then select "Custom" and enter "yyyy-mm-dd" in "Type"

like

Try to convert from text (existing format) to date first,

i.e. =DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2)) <adapt to characters within string>

Then use the date formatting.

helpful

select area, replace "/" with "-"

like

Yeah I was thinking that too but I’m just wondering what the root cause of the issue would be. Wondering what could be preventing it from taking the change?

=TEXT(A1, “YYYY-MM-DD”)

Hmm no luck with that. Thanks for the suggestion though.

Post Photo

This worked!! Thank you so much 👍

What is the value of cell L1128?

Check out the datevalue function too

I’ve had the issue before and I just use & formula to fix it. Never tried too hard to find out what caused it. 😅

What format is the original cell? You might have to use txt to columns and select dte as the format.

Most likely case is that you ran out of custom number formats - sometimes the excel is too bashful to tell you that straight out. You have to make room (delete some).

Additional Posts in Excel Genius

Was promoted to Principle System Engineer in December 2021 (Technical Band 3) and went from 81k to 98k. Our company has experienced significant attrition (13 engineers down to 4 engineers within 6 months) so a few months ago my director bumped me up to 110k based on me being a top performer in 2021 end of year review. I am grateful for the pay increase, but all of the coworkers who left have found positions that provide significantly higher pay. Am I being underpaid for my work?

like
like

Job thread!!! Post job links and also job resources!!!

likeupliftinghelpful

Book(s) that changed your life or had a meaningful impact in your life/see things? Trying to make a list of books to read

likesmart

Would you rather: (1) stay in big law (V100), collect your checks, keep billing 1900+ annually, knowing you’ll likely only progress to non-equity partner but stable pipelines for work; or (2) transition to boutique firm with no billable requirements, no minimum salary, on a pure revenue split (half of collections), option for equity partner in 16 months? In this scenario you’ve been practicing for 10 years but took some time off for babies and to lateral.

like

Anyone have a spare Dribbble invite?

like

How does consultant pay at big 4 compare to industry? What about BCG, Bain, etc?

like

What are your thoughts on 'dark teal' custom suit no patterns/stripes (m here). Which color shirts would go best them.

Many of us here work in real estate somehow:
Rentals, syndication, flips, or as an agent. Have you told your employer about this extra job/side hustle. Why or why not?

like

Scored this today. Anyone had it?

Post Photo
like

Hello connection

I am looking for an better opportunity I am working in investment banking. I have an experience of 3 years in Financial Reporting.

Please let me know any suitable opening is there for my profile. please share so that someone from you connection will help me out

Thankyou

like

How many dispositive motions is a lot to write in a month? I’m a first year and I’ve done three MSJs and two MTDs (including a complex 50 page one) and I feel absolutely drained. That normal?

like

Does Blue Prism platform include attended bots?

helpful

What mask is everyone wearing to the gym? Since the gyms just opened back in NY, I’ve been wearing a cloth mask from VIDA but it gets all wet with sweat - it’s gross. Any masks that are better for working out that you recommend? Thanks!

likehelpful

If you achieve new platinum elite status on Marriott this week, how long does that last for?

like

Anyone thinking of transitioning to buy-side? What are your key considerations?

like

Hello Everyone!
Do you know how to create a non fungible token for pictures?
Thank you!

like

Considering incorporating student management consulting club, any thoughts?

like

You know we are done working when every topic leads to happy hour mentions...

Post Photo
likefunny

Additional Posts (overall)

Bit of a vague Q - but folks who do a volume of DD’s: what best practices for data cleansing & modeling do you employ? I’ve done several DD’s now and feel like a huge chunk of time is due to me re-creating the wheel / not being as smart as I could be in Excel.

like

I run a chatbot that collects students inquiries and want to spot reoccurring questions without going through all 4,000 questions. So far, I’ve created an advanced find keyword indicator to help bucket questions into themes, however I want to build an FAQ sheet. All/any help appreciated 😅

like

Any one good with Tableau I have a question

like

Any tips on the best ways to learn advanced keyboard shortcuts?

like

Sometimes I wish people really understood and recognized what level of Excel mastery it took to solve their problem. I’ll often feel so proud to come up with an elegant solution for a challenge but to them it just exists in a “too hard for me to solve bucket” and they don’t recognize the beauty or skill involved.

likefunny

Hey folks! Could you speak for some best practices and habits y’all incorporated to become absolute beasts when it comes to working on excel purely with keyboard?

like

Hello. I’m wondering if anyone has found a way to reference another worksheet while it is not currently opened (I’ve tried indirect but it doesn’t work).
Because my file location will change quarterly, I have assigned a cell to update with the newest file path. This cell is then used in a vlookup. However, the vlookup only works if the file is currently open.
Seems weird because excel has the exact file path to look in.
Any help would be greatly appreciated!

like

Any insights as to how I can convert data from the first table to the second please? Tried if(xlookup but doesn't work, I think it's because there is more than 1 value. Thank you!

Post Photo
like

Also posted in data analytics bowl, but quick question. I am looking to use Tables 1 and Tables 2 in this screenshot to yield the “ideal outcome” table in Power BI (using query editor). The tables I have setup look similar to Table 1 and Table 2 (it’s a mapping activity of controls to various categories). Is anyone familiar with the proper steps / video that will help me arrive at the ideal outcome?

Post Photo
like

I have about 10 variables each with a certain score (10,14,15, 20,20,20,7,9,2,1). I need the max value is there is a single winner (MAX function work here.) If there is a tie for the highest score, I need an output that says “Multiple.” How would I go about this?

Thanks in advance!

like

My company upgraded to the latest Excel and want to take advantage of the new functions. I have a sheet with several nested INDEX-MATCH’s. I spent some time upgrading them to the new XLOOKUP, but after doing it seems like the sheet runs a bit slow (I have like 20k rows of lookups that seemed to work okay the old way). I’m doing the usual things to make it work faster - not selecting the whole column, using smaller lookup ranges etc. Anyone notices similar slowness with XLOOKUP?

like

Any books to learn advanced excel?

like

Not Excel but Outlook: anyone know how to make it so others can’t recall messages from your inbox?

like

To the data analysts who switched from a Windows laptop to Mac laptop, do you find it is easy to navigate a Mac laptop? I am considering switching from a Windows to a Mac. Many thanks.

like

Has anyone encountered an issue where the tabs on excel flash and excel becomes insanely slow? It only happens on workbooks with lots of tabs and only occurs on external monitors. It's very odd.

What’s the next step in better pay as an HR Coordinator? I’m currently picking up a data analytics certificate for fresher skillsets. I’m aiming for $80-$90k salary at the moment.

Anyone have experience with Excel on MS365? We have people using the same files and this version has no read only lock if someone else is using a file. Is there a check out option and if so, how?

like

For those with MS suite, any apps you’d recommend in addition to standard Outlook, Word, Excel, Powerpoint, Teams? Milestones was OK, but Tasks seemed a useless.

like

Please do excuse how dumbed down the example is, I’ve had to replicate the problem on a much smaller scale with all sensitive data removed.

“Location” refers to a location of stored data and “User group” refers to the user that has access to the location.

I am attempting automate the finding of all instances of where the “U1” user group occurs but exclude it if another user group also has access.
Continued below….

Post Photo
like

I have tables in multiple sheets (cannot put in one) i want to create one consolidated pivot table using all these tables, I tried to use “multiple consolidated ranges” option, but the pivot table is coming in the form of ‘Column’ and ‘Rows’ instead of actual rows and columns (see image) Any solution? TYSM

Post Photo
like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
Download Fishbowl to see what others are saying
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the Fishbowl app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy.

Already have an account? Log in

New to Fishbowl?

image with Fishbowl logo and arms reaching out for a handshake

Already have an account? Log in

By continuing you agree to Terms of Use and Privacy Policy.
Messaging rates may apply

For account settings, visit Fishbowl on Desktop Browser or

General

Legal