Related Posts
Additional Posts in Excel Genius
Anyone know what these badges are?
Visual Storyteller
Coach
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
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.
"" is an unusual email domain. If correct, please proceed to the next step
Messaging rates may apply
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
New to Fishbowl?
Download the Fishbowl app to unlock all discussions on Fishbowl.
Share
Embed this post
Copy and paste embed code on your site
Preview
Download the
Fishbowl app
Scan your QR code to download
Fishbowl app on your mobile
Sign up for free to view this conversation on Fishbowl
Sign up for free to continue using Fishbowl
Messaging rates may apply
Subject Expert
In your database*, create a new column that will be: =DATE(YEAR(expiration_date),MONTH(expiration_date),1)
<this creates a new date, which will be always the first day of the month & year of the expiration date - you can format as MM-YY if you want>
In a new tab, create a table that has:
1. A list of unique month & year that you want to have in your chart (you can use =SORT(UNIQUE(new_column))
2. Count how many active contracts exists for each month & year by using =COUNTIFS(new_column,adjacent_cell,active_column,indicator)
That's it
Legend:
new_column = the column with the first day of the month & year from the expiration date column
adjacent_cell = the month&year from the first column of your new table
active_colum = the column where you have what is active/not active Keys
Indicator = whatever indicator you have for an active contract (if it's the word active, use "active"; if it's the number 1, use 1; and etc.)
* you don't need to actually create a new column
P.s.: if by "date is formatted as" you meant "date input as text, with the format"; you can use =DATEVALUE() to change that to a numerical text that can be recognized by Excel/sheets
Success! Thanks!
Note: the spreadsheet is in sheets for department collaboration. I can usually figure out Excel formulas in sheets, but if you have a sheets version of what I need, even better!
Community Builder
Here you go.
Step1. Standardize your expiration dates to make them easier to count. There's a number of ways to do this, but I use EO month to transform every date to the last day of that month.
Step 2. Build a summary table
Step 3. In that Summary table, reformat the date how you want it to be shown in the chart. No need for two date columns, but wanted to show you what I mean by reformatting.
Step 4. Create chart.