I have a file that I exported from Microsoft Teams that contains a task tracker and several columns of dates. However, I believe these dates must be formatted as text because when I create a pivot table it will not group them by month. I have tried to change the format of the cells to a date with no luck. I have also tried to use ‘text to columns’ to convert them, which causes Excel to crash. This is a small data set, less than 100 lines. Any suggestions? Can share the file if needed.

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

You can add in a column. There's a formula, I think it's just DATE or DATEVALUE. Then use the LEFT/MID/RIGHT formulas to get the digits of the day/month/year into the right spots.

ex. =DATE(LEFT(I2, 4), MID(I2, 6, 2), RIGHT(I2, 2))

likehelpful

If a file crashes like that it means you have an issue with the file itself. Best bet is to move everything to a new sheet and then convert

likehelpful

Save it as a CSV file

like

Just convert the dates (as text) to dates (as date) using either =DATEVALUE(), =MID() or text to columns

But just because your pivot table didn't group them, doesn't necessarily means that the dates are formatted as text. Pivot tables are just crappy

There's a way to force grouping date to pivot tables, but make sure it's formatted as date before trying it

like

You can just add a column with the month or week, if you need to group that way. No need to solve the problem if it takes up more time thar adding these new columns. MONTH() or WEEKNUM()

like

Create a calculated column with =year(date value)&month(date value). Pivot on this value

like

Year(date value)&”-“&if(month value<10),0,””)&month(date value) is how I would prepare my value to pivot on.

Pic1

Post Photo

Pic2

Post Photo

It looks like you have your dates in the PivotTable field for values right now. Do you want them grouped by row/column instead?

Pic3

Post Photo

Upload it into power query and change the column type

Excel on Windows or Mac?

Related Posts

Anyone switch off Zoom video, to smoke while taking/attending a call?

funnylike

I am curious how other inclusion teachers are being utilized when the school is doing remote learning. I find that I am sitting in the zoom class and have very little opportunity to contribute. I am having discussions with my teachers on ways that I can better support them and the students when remote.

like

I was recently offered a Zoom interview. The school had 10 interview slots for me to choose from. Three were one day this week and the rest are next week. I chose the first time slot available. Should I have waited until one of the last slots so I was fresher on their mind when choosing a candidate? I am just sitting here stressing out until I here something. Also, do interviewers still appreciate a hand written thank you for the interview?

Hi Special Educators,
How is your district conducting IEPs?
We’re not allowed to video or use Zoom w/our students (as yet), how are you conducting lessons.
I

like

Contacted by a recuiter

I went to a job fair last week and was excited when a recuiter had contacted me. They scheduled a meeting with me but minutes prior to the meeting I noticed that they did not provide me with their contact information or zoom ID or phone number. Anyways I sent an email stating my concerns and they never got back to me.

Is this normal??

likehelpful

Does Zoom have an employee referral program? If yes, can someone refer me to an open position? Please reach out if so. Zoom

like

How do we get more Klicksters on here? Slack is great and has so many options. But in the spirit of radical candor I feel like people would be even more open to discussion here anonymously.

like

Hi Fishes, I have joined Zoom as a Software Engineer for India team.

likesmart
like

A female friend of mine recently received a settlement in a sexual harassment action against her employer during mediation. Upon receiving her (female) attorney’s final bill she was blown away to discover that her attorney charged her almost five times what the attorney estimated she would charge for the mediation. (The attorney was drinking alcohol during the Zoom mediation and settled for far less than she estimated she would get.) [continued below]

like

Our district voted on returning back to school with students in the classroom and some staying at home. We now have to teach students in the room and on Zoom at the same time. I’m not complaining but does anyone have any tips on the best and most effective way to get this done?

like

Does anybody else think that the Wharton Slack & Discord groups are kinda quiet/inactive compared to admit groups from other schools?

like
like

Is there a tool I can use to record and transcribe meeting/calls (google meet, zoom, teams)?

like

Hi Fishes,

Currently on Notice period need referral.

Will make sure your referrals are best used and not wasted.

Grinded Good amount of DS.

TC: 6L

YOE: 2.5

Highest offer: 20L(startup)

@Linkedin@Twilio@Apple@Zomato@Swiggy@Facebook@Uber@Airbnb@Cohesity@Navi@Mindtickle@Zeta@Gojek@Slack@Harness@Salesforce@Atlassian@Flipkart@Microsoft@Adobe@Salesforce@Twitter@Twitter@Intuit@GoldmanSachs@Curefit@ShareChat@Dunzo@Google@Paypal@Walmart@Ola@Swiggy@Oracle@DpWorld@Oppo@Hotstar@Instabase

like

How should I approach an ominous zoom appointment with supervisinpartner and managing partner? I’m a 3rd year associate. This is a new job, my first day was the first day the whole firm worked from home. It’s also much more challenging and demanding than my previous firm, which is what I wanted, but I haven’t been able to dive in and face the challenge the way I had planned to because I’m really struggling with working from home, both logistically and psychologically....

like

*Judge after instructing several attorneys suit and tie are required even for zoom hearings*

“Wow is there a shortage of ties going on or what”

funnylike
like

Feeling grateful for FaceTime and Skype

Post Photo

More Posts

I once heard that the average age of those that achieved a net worth of a $1M was around 49 yo. Is this intended to include assets like real estate equity or is this meant to be liquid assets / retirement accounts only?
Understanding, $1M is no longer the goal but for those of you that have achieved this already - What was the age you achieved it and are you including your home equity?

like

For Becker users: Is nailing the MCQs the way to go or following the Becker way the best approach? I see a lot of people saying nail the MCQs but most of those people are users of Ninja just curious if that approach work on Becker?

like

What benefits do Hsbc employee gets.

Undandy shoes by indochino or AE?

I just realized the only times I ever feel 'awkward' is with my white friends or colleagues 😭

likefunny

Hi fishes,

What is the entry level CTC IQVIA offers to freshers(Analyst) role?

How to clear techleap? Any suggestions? Dumps?

like

Hi Fishes, I have salary discussion round with McKinsey tomorrow for senior software engineer role. My current CTC is 21 LPA and YOE is 7 years. What is the reasonable salary to ask? TIA

like

Is the Tesla self driving capability upgrade worth it?

likefunny

I am looking for salary above 15lpa in mortgage industry. Even if taking calls i am fine. Please advise

like

How many PTO/Vacation days do you usually take off a year if your employer offers unlimited PTO/Vacation days?

I just started a new job and this is the first time I’ve ran into this. It sounds nice but at the same time I hate the lack of direction. Help!

like

What is EB training for EY staff?

like

I just received an salary adjustment from 112k to 137k and a one time bonus of 25k. I have 5.5 YOE all at Deloitte. Should i stay or leave?

like

Anyone on here dealing with dramatastic in-laws currently? “Love” marriage, 4 halal pre-marriage years (practicing Muslims, indian, and I’m a professional). Mine are insane - his mom said she’d disown him before the wedding, and his sisters went along with it. I have not heard from them since. His mom had this idea of his sisters (both younger than my husband) getting married before him. He’s since reconciled with his family but they literally don’t bother to even talk to me 🙄

like

anyone know about the history of gov contracting? someone mentioned Bush signing some law during his admin. is this when contracting blew up?

like

Planning a 1 week trip to the Pacific Northwest (hopefully very hiking heavy). Would you recommend going to Washington or Oregon? It seems as if combining them both into a week would be a crunch.

like

Is there an option for Product Manager mock interview? Would really like to get some feedback before I start applying.

like

Is it smart to wait to buy a house because home prices are through the roof at the moment? They’re inflated. This needs to stop!

likefunny

My home office had a makeover and I upgraded to a standing desk! I now have a box of books and college textbooks ready to donate. Is there anywhere I could take them to donate considering... South Fl

like

Anyone know of any Sr. AppSec position openings ? TIA

like

Additional Posts in Excel Genius

Experience a lot of downtime because I deal with massive amounts of data and need to constantly filter and delete records that are not relevant. Is there a workaround for this?

like

Is there a way to use formulas within icon set-based conditional formatting?

I'm trying to compare D2 vs. C2, E2 vs. D2, etc..

I know I could create a helper table w values

like

Any book recommendations on excel modeling for real world business applications?

like

Can you share the most useful formulae and built in automation features you have used with the latest version of excel?

like

I have two columns in Excel, let’s call them A and B. I want to see if the numerical IDs in column A are also captured in column B or not. What formula would help me get this?

like

A lot of the work at my new employer is quite redundant and can be automated via macros. What are some of the best resources to learn vba?

like

I’m trying to extract numbers from a string. I googled a formula, however the formula rounds up the last two digits of a 16 character string. Is there are a formula I could use eithoutcharacter limit?

Struggling with this:
Suppose I want to shortlist companies that serve the priority industry vertical of a country. So I have the industries served by company (one column for each industry) and (cont)

Does anyone have any tips/tricks for knowing where to plug +1/-1 in a large table with totals across the y and x axis that contain un-rounded numbers? Every time I fix a row I throw off a column or my check figures to the true data source.

Post Photo
like

Advice needed: I have a manager that I do PM work for, including metrics, and often get asked to “re-run the numbers” but this is an incredibly complex process that takes a lot of time to make accurate in excel, even though it’s a single-figure output on a team/teams progress

Is there any way to make them realize the effort that goes into it on my side? I’ve stated before but they mostly brushed it off - worried I backed myself into a corner by not making it sound hard enough the first time

like

Does anyone know if there is an excel plug-in that links brokerage data (i.e. account info from fidelity/Schwab, etc.) so I can track/analyze on excel?

likefunny

Excel's Conditioning Formatting has a mind of it's own, or is possessed. You define the rule, designate the reference cell/range (goodness knows NOT to use arrow keys within the Edit Rules box... cont

like