I have a large spreadsheet with lots of dates, how can I identify classification based on the dates below? I.e. if your hire date was in between these parameters, it flags you. nested IFAND?

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

Sort high end of range from highest to lowest and use vlookup , approximate match.

I might have it backwards so just Google approximate match vlookup.

like

This does not work. The first part of the formula does, but when I add the second (10 year) it gives me error

Post Photo

Time elapsed formula between 2 dates =

=DATEDIF(A2,B2,"Y") & " Years, " & DATEDIF(A2,B2,"YM") & " Months, " & DATEDIF(A2,B2,"MD") & " Days"

Or just stop at years to see how many years. The second date can be hardcoded to a cell with =today()

I don't have the perfect solution but maybe this will work for you:

1. Assign a number to these dates.
2. I have created these from/to cells using vlookup. If you enter 2 dates in the yellow cells, it'll mention the values next to the dates.
3. I have added a conditional formatting rule to highlight cells if they are in-between a certain range. Here you see cells from 1-5 highlighted since values in cell G2 and I2 are 1 and 5

Hope this helps 😊

Post Photo

You could try using conditional Formatting

Related Posts

Hi can anyone refer me to this role.
ITIL Change management analyst senior
JR217055

like

What will be the best gaming laptop guys ?
I'm looking for a thin and light laptop with good looks and specs. My budget is 50K

like

Deloitte sponsored my green card. Need 1-year experience from previous employer in job requirements. Deloitte is my first job since graduation (college internships was less than 6m in total) Two years at A and less than 1 year at C. Working w the PERM team to solve but wanted to get some thoughts.

like

Hi folks, hoping someone can help me out. I got an offer from IBM India and I saw that there are two components in the salary. The reference salary and the retirals.

Is the retirals part of my CTC? Will the PF deduction be part of that or will the PF be deducted from the reference salary?

HBO Max announced they will remove 36 titles from their platform. I hate it when this happens, especially when I'm in the middle of a show and suddenly, after going through so many episodes, it gets pulled from streaming.

like

Does FAANG / MANGA check for DS/Algo/Sys-Design knowledge while interviewing experienced developers of about 6+ years? Or Would it be any different?

like

Does your school allow you to send out a supply list? I have to buy all the student supplies for my SPED classroom.

like

Ever find it hard to distinguish flirting from friendliness?

like

Where is the HCL office in Mumbai? Can anyone tell whats the process on onboarding/joining day?

like

Are there any PE funds based in Geneva?

like

Does Amazon have tuition reimbursement for salaried employees? Can anyone provide numbers? Thanks

like

And here I've been going into interviews hunched over and sobbing. Who knew it was about JUST BEING CONFIDENT?? Duh. #ThoughtLeader

Post Photo
funnylike
like

Attending my first virtual recruiting session event. How does one prepare for it? Since I think it’s gonna be done in groups.

like

Where would you say is the best place to raise a family as a gay, wealthy couple? A place that accepts LGBT but also is ok with people who have accumulated wealth and don’t hide it.

For example, Dubai is great for wealthy people but terrible for LGBT. On the other hand, Denmark is great for LGBT but terrible for showing your wealth.

likefunnyhelpful

Went through all Tiktok rounds (1 recruiter, 3 PM, and 1 HR). Wrapped up my final interview with HR 2 weeks ago and they said that they would have an update by the end of the week, but to reach out to the recruiter the following Monday if I didn’t get an update.
I followed up last Monday and the recruiter told me they’re still interested, but they’re awaiting further guidance from the hiring team. Didn’t hear from her again, so I followed up yesterday and no reply. Any feedback about this?

like

[Legal Tech] Hi there. I practiced at a big law for a couple of years and now have been moved in-house with a focus on Legal Tech (dealing with platforms, social media - responsibilities, safe habour, Ts&Cs, e-money flow, etc) I really love the job now and finally find the path and passion. I do like working on legal tech matters a lots. Anyone on the same major? Please share your thoughts :) TIA

like

My contribution for the day

Post Photo
likefunnyhelpful

Additional Posts in Excel Genius

I’ve heard of power query/pivot multiple times but what are their advantages compared to traditional excel? Anyone can share some examples?

like

Tips on how to quickly learn mid/advanced Excel tricks and shortcuts? Forum or training somewhere?

like

Does anyone know how to keep in leading zeros when converting to a number, so I can do a vlookup across data sets?

like

Not Excel related, but any folks in strategy willing to suggest the best and most useful data platform to learn? I’m thinking along the lines of Tableau, Power BI, etc.

likehelpful

Does anyone here use ASAP Utilites as an Excel add-in? This thing is amazing.

like

I did this manually but would there be a way to pivot the dates (day/month) in the rows years in columns with raw data being (date/month/year) needed a visual of same day different year

Post Photo

How would you create this formula based on picture. In cell C6 I want to return a value ; if (B6 is in column G, and C4 is in column H, then return (formula). Thank you

Post Photo
like

I'm stumped!
Coworker creates "Sheet A" with invoice numbers
I use Monarch to extract invoice #'s and amounts from another report.
I do a pivot table to compile amounts by invoice. I copy those results to "Sheet Z".
I then do an @vlookup on Sheet A referencing Sheet Z (just the invoice # and amount).
It isn't working unless I go to Sheet Z and type over the invoice # (same number)! All invoice #'s are absolute and in the same font & alignment. Workbook has 10 yrs data. Has worked in past!

like

Just wanna say thank you to this group of beautiful people, always willing to help each other reach a solution!

likeuplifting

What are the most useful macros you’ve seen that could be used across multiple projects?

like

Hi, two of my colleagues keep getting a run-time error 91 when trying to run a macro on this worksheet. However, I am unable to detect the issue nor find a solution as this does not happen to me when trying to run the same macro. Any way I can find a solution?

like

Does anybody have a good resource that would let me link data from QuickBooks Desktop to Excel?

like

Best way to visually display trending data on sales when looking at how a few factors influence volume and price? Also any tips for including both in the same chart?

Really wish my clients didn’t use Google Sheets. This sucks.

like

My excel columns have switched to numbers (so I know have numbers for columns and rows). This has also made all of my formulas incredibly confusing. Was this part of some update? How can I switch my columns back to letters?

like

Hi,
I am trying to match in a specific row of a different sheet, although I want to use a calculated row value to specify the array to look in, see the photo for the logic I am trying to do… any ideas on how to get this to work?

Post Photo
like

How can I make these dates sortable with a filter?

Post Photo
like

What scenarios would you use offset vs. and index/match formula? Also, what does including >1 [width] do to the offset formula?

Im trying to grasp why if I isolate the offset formula (currently being used within a sum product), I’m getting a spill error.

like

Hello All,
Excel help please. I’m trying to combine 3 tabs into 1. So on tabs a,b,c I have wire instructions that go to different places, but they all have the same field names/format. However the number of rows for each tab will vary from day to day. I need to make a fourth tab that shows all the rows from a,b,c consecutively. Because the number of rows change, I can’t find the best way to collate all the rows into one table. I can use a macro or lookups or a source formula. Please advise!
Mo

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
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

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

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

Messaging rates may apply

Download 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

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal