This is an overview:

**The YEARFRAC Function in Excel**

** Function Objective**

The YEARFRAC function is used to calculate the fraction of the year represented by the number of the whole days between two dates.

** Syntax**

**=YEARFRAC(start_date, end_date, [basis])**

**Arguments **

Argument | Required/Optional | Explanation |
---|---|---|

start_date |
Required |
The date from which the calculation begins |

end_date |
Required |
The date in which the calculation ends |

basis |
Optional |
While calculating the fractional year, the type of day count basis is needed |

Different types of [basis]:

Basis |
Description |
---|---|

0 or omitted |
30 day months/360 day years(U.S. – NSDA) |

1 |
Actual days in the months/Actual days in the years |

2 |
Actual days / 360 days per year |

3 |
Actual days / 365 days per year |

4 |
30 days per month / 360 days per year(European) |

**Method 1 – Use the YEARFRAC Function in Excel Without Basis**

The dataset showcases **Employee Names,** their **Joining Date**, their **Resign Date** and the **Duration** of their work.

Calculate the working time:

**Step 1:**

- Select
**E5**, go to**Formula Bar,**and enter the formula:

`=YEARFRAC(C5, D5)`

**Step 2:**

- Press Enter.

This is the output.

**Step 3:**

- Drag down the Fill Handle to see the result in the rest of the cells.

**Method 2 – Applying the YEARFRAC Function With Basis**

**Step 1:**

- Select
**F5.**

**
**

- Enter the function in the
**Formula Bar**:

`=YEARFRAC(C5,D5,E5)`

**Step 2:**

- Press
**Enter**. A

The function will return **84722222**.

- Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

In** F10** and** F11, **the **#NUM! Error **is displayed: the **Basis < 0 **or **Basis > 4**.

**Method ****3 – Perform the YEARFRAC Function to Calculate Age**

**Step 1:**

- To find the John’s age, enter the formula in
**C5**:

`=YEARFRAC(C5, D5)`

- Press Enter.

The output is 22.125.

**Step 2:**

- Drag down the Fill Handle to see the result in the rest of the cells.

**Method ****4 – Using the YEARFRAC Function to Find Working Time in Companies**

To calculate John’s working time:

**Step 1:**

- Select
**F5**and enter the**YEARFRAC Formula:**

`=YEARFRAC(C5, D5, E5)`

**Step 2:**

- Press
**Enter**.

The output is **847222222**.

- Drag down the Fill Handle to see the result in the rest of the cells.

In **Row 5 **and **Row 6, **you can see the **Joining Date** and **Resign Date **of **John **and Mary is the same, but their working time is different, due to the **Basis.**

** **

**Method ****5 – Use the INT Function and the YEARFRAC Functions in Excel**

To calculate the **Employee’s** age in **Integers.**

**Step 1:**

- Select
**F5**and enter the following formula:

`=INT(YEARFRAC(C5, D5))`

- Press
**Enter.**

**John’s **age is **22.**

** ****Step 2:**

- Drag down the Fill Handle to see the result in the rest of the cells.

** **

**Method ****6 – Calculate the Percentage of the Year Using the YEARFRAC Function**

**February 28, 2018 **was used as starting date to calculate the time passed in this year.

The formula is:

**
**

`=YEARFRAC(DATE(YEAR(B5),1,1), B5)`

**February**ends, 0.16 or 16% of the year has passed.

**Formula Breakdown:**

The YEAR function refers to the year of a date: =YEAR(B5) The DATE function needs three arguments: the year, the s month and the day. As B5 was selected, the formula becomes: =DATE(YEAR(B5), 1,1) Here, YEAR(B5) refers to 2018, 1 represents January, and the other 1 represents the first day of the year. The final formula will be: =YEARFRAC(DATE(YEAR(B5), 1, 1), B5)

You can also calculate the percentage of **June 30, 2018**, and **September 30, 2018**:

**Method ****7 – Applying the IF Function and the YEARFRAC Function in Excel**

Check John’s working hours.

- Select
**F5**and enter the formula.

`=IF(YEARFRAC(C5, D5)>5, “Permanent”, “Provisional”)`

- Press
**Enter**.

John is a **Permanent Employee, **with more than **5** years in the company.

**Step 2:**

- Get the results for the other
**Employees.**

** **

**Things to Remember**

**→ #NUM! error** –

**Basis < 0****Basis > 4**.

**→ #VALUE! error **–

**Arguments**are not a valid Date- The
**Basis**is non-numeric.

**Download Practice Workbook**

Download the practice workbook.

**<< Go Back to Excel Functions ****|**** Learn Excel**