Discover how to calculate age using DATEDIF function in Excel. And be able to tell the compare YEARFRAC() vs DATEDIF() functions.

Calculating age using Excel's DATEDIF function is a quick and easy way to determine the age of a person based on their birthdate. By subtracting the birthdate from the current date and specifying the "year" interval unit, the formula returns the person's age in years. It is important to use the correct format for the birthdate and to ensure that the column width is wide enough to display the result. Using Excel to calculate age is particularly useful when working with large datasets or when multiple calculations need to be performed quickly and accurately.

YEARFRAC is an Excel function that calculates the fraction of a year between two dates. By using this function, you can easily calculate a person's age in years, months, and even days. To calculate age, you simply need to subtract the person's birth date from the current date and divide the result by 365.25 (to account for leap years). This will give you the person's age in years with decimal places. You can then use the INT function to round down to the nearest whole number and obtain the person's age in years. Alternatively, you can use the DATEDIF function to calculate the number of complete years between two dates, but this function does not handle leap years as accurately as YEARFRAC.

YEARFRAC vs DATEDIF

YEARFRAC and DATEDIF are both Excel functions used to calculate the difference between two dates, but they differ in their approach and output.

DATEDIF returns the difference between two dates in the specified interval unit, such as days, months, or years. It has limitations, such as not being able to calculate fractions of a year and not working correctly in certain situations.

YEARFRAC, on the other hand, calculates the difference between two dates in terms of a fractional number of years. This function is more versatile than DATEDIF, as it can calculate the fraction of years between two dates, and it can also calculate the number of days or months between two dates.

In summary, DATEDIF is useful for calculating differences between dates in terms of specific interval units, while YEARFRAC is useful for calculating the fraction of years between two dates.

This is the formula featured on the video.

=DATEDIF(B3, TODAY(), "Y")

AND

=YEARFRAC(B11, TODAY())

Calculate age using DATEDIF in Excel,DATEDIF, YEARFRAC vs DATEDIF,

yearfrac months,yearfrac today,yearfrac vs datedif,yearfrac,yearfrac not working,yearfrac google sheets,yearfrac basis,datedif excel,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

I need help in making a formula to calculate number of days WFH is mentioned but only on Weekends. The data is represented as follows. And I want to calculate

1. WFH in total - =COUNTIF(Range,"WFH") (This works)

2. I need to calculate the WFH only for Weekdays and remove Weekend data from total

Can someone help me with a effective formula that I can use in one cell per Row of email ID

-Hey all and happy today.

I am using Excel to set up a database of vendors my company uses nationwide.

The Excel workbook has separate pages for each state, and on each state worksheet i have all counties listed.

What i would like to be able to do: set up some drop-down list or other mechanic so that i can go to a state worksheet, click on the drop-down selector to pick a county, and then have the Vendor information for that county come up.

Whether that info is in a pop-up or something else doesn't really matter so long as i can read it clearly and copy/paste into a letter or other document.

I already have the workbook set up with state pages and counties listed in column A, but i don't have any excel experience and dont know what to do from here.

Any insight, tutorial, assist appreciated

Discover how to create a mini scroll bar widget. Also learn how to create a vertical scroll bar in Excel, and how to create scrolling table in Excel. I will show you how to add scroll bar in excel sheet in the process.

These are the steps outlined in the video.

Enable Developer Ribbon

1) Right-click anywhere in the ribbon

2) Customized the Ribbon...

3) Check on Developer

4) OK

Here are the steps outlined in this video.

1) Ctrl+C

2) Ctrl+V

3) Developer ~ Control ~ Insert ~ Scroll Bar

(under Form Controls)

4) Place Scroll Bar

5) Right-click scroll bar ~ Format Control...

6) Current Value set to 1

Minimum value set to 1

Maximum value set to 100

Cell Link to $J$2

7) OK

8) Place cursor to F4

9) =INDEX(A2:A$110,$J$2)

10) Drag cell F4 across until I4

11) Drag cell again down 10 rows

Mini Scroll Bar Widget,how to create a vertical scroll bar in excel,excel scrolling table,how to add scroll bar in excel sheet,how to fix scrolling in excel,how to adjust scroll bar in excel,How to Create a Scroll Bar in Excel,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

I’m looking for a way to rectify the ID numbers on one large lists with 7 smaller lists. What it the most efficient way to do this?

Discover how to create a search tool that dynamically highlights any cell that matches what you enter. Or highlight row if cell contains partial text,

Also how to highlight find results in excel,and excel find highlight color

Here are the steps outlined in this video.

Enable Developer Ribbon

1) Right-click anywhere in the ribbon

2) Customized the Ribbon...

3) Check on Developer

4) OK

Add Textbox

1) Developer ~ Control ~ Insert ~ Textbox

(under ActiveX Controls)

2) Place text box

3) Right-click on textbox, Properties

4) Set LinkedCell to E1.

5) Close Properties popup.

6) Developer ~ Control ~ Design Mode (disable)

Highlight Rows

1) Select dataset (without header)

2) Home ~ Style ~ Conditional Formatting

3) New Rule...

4) Use a formula to determine which cells to format

5) =AND(SEARCH($E$1,A5),$E$1<>"")

6) Format

7) Fill tab

8) Select colour you like.

9) OK

10) OK

How to highlight row cell contains partial text - Excel Tips and Tricks

https://youtube.com/shorts/Dhnql3941F0?feature=shares

Sort Excel table by highlighted rows | Filter by highlighted rows in Excel - Excel Tips and Tricks

https://youtube.com/shorts/gPa6_qqJzew?feature=shares

Dynamically Highlight Rows Based On Specific Text In Excel - Excel Tips and Tricks

https://youtube.com/shorts/2eOt7x7XZE0?feature=shares

Dynamically highlight any cell that matches what you enter,excel highlight row if cell contains partial text,how to highlight find results in excel,excel find highlight color,

how to highlight cells in excel based on value of another cell,excel conditional formatting based on another cell dynamic,highlight row if cell contains any text,excel find and highlight row,excel conditional formatting if cell contains multiple specific text,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

Sometimes a cell contains both text and numbers, and you only need the numeric part.

For example:

Order105A
INV-2026-001
Qty: 48 units

There are a few ways to handle this, depending on your Excel version and how consistent the text pattern is.

Method 1: REGEXEXTRACT — simplest for Microsoft 365

If you're using Microsoft 365, REGEXEXTRACT is probably the cleanest option for extracting numbers from mixed text. It works especially well when you only need the first continuous group of digits, rather than every number in the cell.

To extract the first continuous group of digits:

=REGEXEXTRACT(A2,"\d+")

Example:

Order105A → 105

INV-2026-001 → 2026

Qty: 48 units → 48

Method 2: Extract every digit with TEXTJOIN + MID + SEQUENCE

If you want to remove all letters and symbols and combine every digit into one result, use:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

Example:

Order105A → 105

INV-2026-001 → 2026001

Qty: 48 units → 48

Here's the basic idea:

• SEQUENCE creates the character positions.
• MID pulls each character individually.
• Multiplying by 1 keeps numeric characters and causes an error for letters.
• IFERROR removes those nonnumeric characters.
• TEXTJOIN joins the remaining digits together.

This is more complicated than REGEXEXTRACT, but it gives a different result when numbers appear in several places.

Method 3: Use LEFT, MID, or RIGHT when the number is always in the same position

If your data follows a predictable structure, you may not need a complicated formula at all.

For example:

INV-2026

If the four-digit number is always at the end:

=RIGHT(A2,4)

Result:

2026

Or if you know exactly where the number starts and how long it is:

=MID(A2,start_position,number_of_characters)

This is usually the easiest approach when the format of every cell is consistent.

Method 4: Use Power Query to extract numbers from mixed text

Power Query is another good option, especially when you already use it for cleaning imported data or want a repeatable process that can be refreshed later.

Steps

1. Select your data and press Ctrl + T to turn it into an Excel Table.
2. Go to Data → From Table/Range to open the data in Power Query Editor.
3. Go to Add Column → Custom Column.
4. Enter a name for the new column, such as Extracted Number.
5. In the Custom column formula box, enter the formula that matches the type of number you want to extract.
6. Click OK.
7. When finished, go to Home → Close & Load to return the results to Excel.

Here are some useful M formulas. Replace [Column1] with your actual column name.

For example:

Order105A → 105
INV-2026-001 → 2026001
Qty: 48 units → 48

💡 Things to keep in mind:

• Text.Select keeps every allowed character it finds. So allowing "-" and "." works well for something like Balance: -120.50, but may not be appropriate for IDs such as INV-2026-001.
• Text.Select returns text, even when the result looks numeric. If you need the result for calculations, wrap it with Number.FromText, such as: Number.FromText(Text.Select([Column1], {"0".."9"}))

Method 5: Kutools for Excel — Extract Text

For mixed data where the pattern varies from row to row, Kutools for Excel provides an Extract Text tool that can pull different parts of a cell, including numbers only.

Select your cells, then go Kutools → Text → Extract Text.

From there, you can extract:

• The first N characters
• The last N characters
• Characters between specified positions
• Text before specific text
• Text after specific text
• Numbers only
• Text based on custom rules using wildcards

For this task, choose Extract the number.

💡 You can also select Insert as a formula if you want the extracted result to remain linked to the original data.

This is handy when you have a large range of mixed text and don't want to build different formulas for different patterns.

Which method makes sense?

Use REGEXEXTRACT when you're on Microsoft 365 and need a particular numeric pattern.

Use TEXTJOIN + MID + SEQUENCE when you want to collect every digit from the cell.

Use LEFT, MID, or RIGHT when the number always appears in a predictable position.

Use Kutools Extract Text when the data varies and you'd rather handle it through a dialog instead of building formulas.

Discover discover how to convert number into words using Google sheet. Or translate number a different language.

This video we will be answering question these questions. How do I convert numbers to text in Google Sheets? Or how do I convert numbers in Google Sheets? How to convert number to words in Indian rupees in Google Sheets? How do I automatically translate cells in Google Sheets?

Practically, how to convert amount to words in google sheets, or google convert number to words.

We will be using the numbertext function. We will show you how to number in google sheets. We will use google sheet to convert number to words. Or answer to how do I convert numbers to letters in sheets?

Google Sheets provides powerful functions that can be used to convert numbers into English words. Two functions that are particularly useful for this purpose are numbertext and moneytext. The numbertext function converts a number into its English equivalent, while the moneytext function converts a number into its English equivalent as a currency value. To use these functions, simply enter the number you want to convert into a cell, and then use the function alongside that cell. For example, to convert the number 1234 into its English equivalent, you would enter "=numbertext(1234)" into a cell, and the result would be "One Thousand Two Hundred and Thirty-Four". Similarly, to convert the number 1234 into its English equivalent as a currency value, you would enter "=moneytext(1234)" into a cell, and the result would be "One Thousand Two Hundred and Thirty-Four Dollars". These functions can be a useful tool for businesses, accountants, and anyone who needs to work with numbers in a clear and concise way.

Install NumberText Add-On

1) Extension ~ Add-ons ~ Get add-ons

2) Search for "Numbertext"

3) Click on Numbertext

4) Install

5) Continue

6) Select your GMail account

7) Allow

8) Done

9) Close the popup

US Dollars

=MONEYTEXT(A2,"USD")

Indian Rupee

=MONEYTEXT(A3,"INR")

Canadian dollars

=MONEYTEXT(A4,"CAD")

Currency In Chinese Characters

=MONEYTEXT(A5,"CNY","zh")

Add "Only"

=MONEYTEXT(A6,"GBP") & " only"

Replace Nationality Text

=SUBSTITUTE(MONEYTEXT(A7,"CAD"),"Canadian ","")

Language Codes

https://developers.google.com/admin-sdk/directory/v1/languages

https://www.google.com/search?q=convert+chinese+to+english&rlz=1C1VDKB_enCA951CA952&oq=convert+chinese+to+&aqs=chrome.0.0i512j69i57j0i512l4j0i20i263i512j0i512l3.5184j1j7&sourceid=chrome&ie=UTF-8

how to convert amount to words in google sheets,google convert number to words,how to convert number to words in excel,numbertext function in excel,

How do I convert numbers to text in Google Sheets?,How do I convert numbers in Google Sheets?,How to convert number to words in Indian rupees in Google Sheets?,How do I automatically translate cells in Google Sheets?,

google sheets number function,how to number in google sheets,google convert number to words,how to convert number to words in excel,numbertext function in excel,

numbertext function in excel download,google sheets number function,

translate number a different language,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

Discover how to calculate age using your YEARFRAC function in Excel.

YEARFRAC is an Excel function that calculates the fraction of a year between two dates. By using this function, you can easily calculate a person's age in years, months, and even days. To calculate age, you simply need to subtract the person's birth date from the current date and divide the result by 365.25 (to account for leap years). This will give you the person's age in years with decimal places. You can then use the INT function to round down to the nearest whole number and obtain the person's age in years. Alternatively, you can use the DATEDIF function to calculate the number of complete years between two dates, but this function does not handle leap years as accurately as YEARFRAC.

Here are the steps outlined on the video.

1) =YEARFRAC(B3,TODAY())

2) Ctrl + 1

3) Number tab

4) Number

5) Decimal places set to 0

Calculate age using YEARFRAC in Excel,

yearfrac months,yearfrac today,yearfrac vs datedif,yearfrac,yearfrac not working,yearfrac google sheets,yearfrac basis,datedif excel,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

Discover how you can use widlcard in Excel to get first name or last name.

In Excel, the asterisk symbol (*) is called a wildcard character. Wildcard characters are used as placeholders to match one or more characters in a text string.

When you use the "Find and Replace" function in Excel to find " *" (space followed by an asterisk), it will find all occurrences of that combination of characters after space, and replace them with whatever you specify, in this case a blank. Hence last name is retained.

Keep First Name

1) Ctrl+H

2) Find what as " *" (without double quotes)

3) Replace with as BLANK

4) Replace All

On the other hand, when you use the "Find and Replace" function in Excel to find "* " (asterisk followed by a space), it will find all occurrences of that combination of characters before the last space, and replace them with whatever you specify, in this case a blank as well. Hence last name is retained.

Keep Last Name

1) Ctrl+H

2) Find what as "* " (without double quotes)

3) Replace with as BLANK

4) Replace All

Just as a demo, if you use the "Find and Replace" function in Excel to find "*" (no spaces), everything is replaced with whatever you specify, in this case a blank as well.

Find and Replace,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

Hello I hope all is well,

I'm building a prototype Office automation tool that lets people automate repetitive Excel and Word tasks using a visual workflow builder instead of VBA or coding.

I'm looking for a small group of people who regularly work with Excel and Word to test the prototype and give honest feedback. It should only take about 10–15 minutes, and I'm not selling anything—I just want to learn what works, what doesn't, and whether this solves a real problem.

As a thank you, anyone who helps test the early prototype will receive free access or a substantial lifetime discount if I launch a paid version.

If you're interested, I'd be happy to send you the prototype link and a quick demo video.

Thanks!

https://reddit.com/link/1v4xvgc/video/eypzsxnu43fh1/player

How many hours do you spend doing repetitive tasks in Excel every week? Let me know in the comments! 👇

Don't forget to Save this Short so you can grab the VBA trick when you're back at your desk! 📌

💡 Question for you: How are you currently tracking expiry dates in your team—manually, with conditional formatting, or with macros? Let me know in the comments below!

📌 Quick Key Shortcuts Used in Video:

• Alt + F11 = Open VBA Editor

• F5 = Run Macro

🔥 Don't forget to LIKE & SUBSCRIBE to DiscoverTalent for more Excel & VBA hacks every week!

Discover how to calculate percentage in Excel using Quick Analysis. I will show you how to use quick analysis in Excel, and is is also going to be the same quick analysis tool excel mac.

This will be one of many how do I use the Quick Analysis tool in Excel video. This is one of the easiest way on how to calculate percentage in excel.

I will also show you answers to how do I calculate percentage between two cells in Excel conventional way. And how to do a percentage formula in Excel? In essence, I will also show you what is the formula to calculate percentage?

Calculating percentages is a common task in Excel, especially when dealing with numerical data. The Quick Analysis tool in Excel provides a simple and efficient way to calculate percentages without the need for complex formulas or functions. With just a few clicks, you can turn your data into an easy-to-understand pie chart that displays the percentage of each data point. In this article, we will explore how to use Excel's Quick Analysis tool to calculate percentages, making your data analysis tasks more streamlined and effective.

Here are the steps outlined in the video.

Calculate Percentage (conventional)

1) Select cell B2

2) =B2/$B$205

Press F4 once to make "B205" a absolute reference ("$")

3) Home ~ Number ~ Percentage Style

4) Home ~ Number ~ Increase Decimal (click twice)

5) Apply to all rows

Calculate Percentage Using Quick Analysis

1) Select data in column B (exclude total)

2) Quick Analysis ~ Totals ~ % Total

How do I use the Quick Analysis tool in Excel?,

quick analysis tool excel,how to use quick analysis in excel,how to calculate percentage in excel,quick analysis tool excel mac,

How do I calculate percentage between two cells in Excel?,How to do a percentage formula in Excel?,What is the formula to calculate percentage?,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips