The formula will look like this: = WEEKDAY ( A2 ,2) In cell F2 create a formula that calculates the week number of the date in A2. The value 1. Steps: Firstly, select the cell where you want to calculate the End Date. =INT(YEARFRAC(birth date,TODAY(), 1)) Okay, enough technical talk, let’s try the formula with some real data 💪. 5. Considering the above given example, if you want to know the service of the employee in years and months, add the below given formula to one blank cell. Note: At the time of this writing, the current date is September 2, 2021. YEAR function in Excel. Now, in the third argument, you need to enter the unit “y” to get the count of years. The TODAY function in Excel returns the current date, while the NOW function returns the current date and time. Step 2: Now Select the cell where you want the age of the person. Firstly, we need to select the cell wherein we will input the years of service. ie add up the values in the Bank Expenses column of all those entries dated between between 1st and 30th April. Type: =INT (YEARFRAC (. What fraction of the year was this?Here, we will use the formula =INT (YEARFRAC (C3,TODAY ())) in cell D3 and drag it down till cell D12, which gives the result: The TODAY function gives today's date, which the YEARFRAC function uses to find the fraction between both dates. Return specific month and day values with the EDATE, DAYS, and DAYS360 functions in Excel. NOW Function; SECOND Function; TIME Function; TIMEVALUE Function; TODAY Function; WEEKDAY Function;. YEARFRAC - the fraction of the year between two dates. Returns the day for a DateTime value. LinkBack. . Basis - use basis 1 that tells Excel to divide the actual number of days per month by the actual number of days per year. g. To calculate a decimal number that represents the number of months between two dates, you can use the YEARFRAC function like this: =YEARFRAC(start,end)*12 YEARFRAC returns fractional years between two dates, so the result is an approximate number of months between two dates, including fractional months returned as a decimal value. 2. For each function listed below, I’ll define it first, then show the function’s arguments, which are the values that functions use to perform calculations. Calculates the number of days between two dates without using cell references, which is 153. Step 1: Enter the DOB of a person in the cell. Basis: Use basis 1 which tells Excel to divide the actual number of days per month by the actual number of days per year. Let’s see how you can do it. Age = INT ( YEARFRAC ( Customer [Birthdate], TODAY (), 1 ) ) Copy Conventions # 4. Calendar Generator – Create a calendar for any year. YEARFRAC(start_date, end_date, [basis]) To get a whole number only (not rounded), you can use the INT function like this: = INT ( YEARFRAC (A1,B1)) // whole number only, discard decimal. YEARFRAC takes two dates and provides the portion of the year between them. 3 months. The start date is provided by cell C4. =YEAR (NOW ())-1978: This formula calculates the age of an. 5 =YEARFRAC (A5,B5) Result: 1. Difference in Years = 2020 - 1985 = 35 years. The method to calculate the YEARFRAC depends on the convention. =YEARFRAC(DATE(2021, 1, 1), DATE(2021, 12, 31)). Learn how to use special date functions: TODAY, NOW, YEARFRAC, and DATEDIF. Rather than display the answer as a decimal, I require to show Years, Months and Days. Any time element is ignored. Countdown to Any Date – Create your own countdown. 455. This free date calculator computes the difference between two dates. In the above example, we have the 1st March 2021 as the start date and 23 Feb 2022 as the end date. Hit Enter, and you. Tombstone Date Math can also be used to calculate the length of time since an event occurred, such as a birthdate or an anniversary. Once you enter your dates, you can calculate the age in years and months. Applying the YEARFRAC Function. 14 years) =INT(YEARFRAC(B2, C2)) Each formula assume that the start date is in cell B2 and the end date is in cell C2. The Excel DAYS function. txt","path":"C2 Excel Skills. e. Here, I selected cell E5. Here, I will use the EDATE function to calculate the End Date. With the help of the Today function in Excel, I need to find today’s or current date in the cell “D8”. The YEARFRAC Function works exactly the same in Google Sheets as in Excel: YEAR function in Excel. Test Prep. ) WEEK( date <, 'descriptor'>) returns the week of year from a SAS date value. Click the insert function button (fx) under the formula toolbar; a dialog box will appear; type. Below I again have the same data set and I want to calculate the duration of service of each employee in years and months. After putting the data of the current year (your end date) and the person’s birthdate (your start date), Microsoft Excel will give you the answer of 45. To use the YEARFRAC Function with Basis, you now follow the steps which have given below: Step 1: First, select Cell F5. To do this, first right-click drag the field date into the view. TODAY (Available in Excel 2007 and subsequent versions) Date Calculations (NOW, TODAY, YEARFRAC) Use the YEARFRAC function to work out how many years difference is there between the date 1980-07-31 (31 July 1980) and 1998-05-02 (2 May 1998) (to 1 decimal place)? Extract the current date and time with the TODAY and NOW functions in Excel. This is an example of nesting. The first one is used to calculate the total number of completed years. Click the cell containing the end. YEARFRAC(start_date, end_date, [basis]) Parameters: Name Description Data Type Required/. sql-server-2008. Use the sample formulas in this topic to manipulate and perform calculations with date and time. To return the percent of year completed based on the current year and current date, you can adjust the formula to use the TODAY function like this: = YEARFRAC ( DATE ( YEAR ( TODAY ()),1,1), TODAY ()) In this version, the TODAY function returns the current date which goes into YEARFRAC twice – once as the start date, and once as as the end date. Non-Array Formulas: Cell A1 contains 31/12/2000. Click the cell containing the start date, then type a comma. Since the next year is a leap year, there are 366 days between 31-Jul-2015 and 31-Jul. You are going to discover a whole range of skills and techniques that will become a standard component of your everyday use of Excel. YEARFRAC: Returns the year fraction representing the number of whole days between start_date and end_date. For convention = 2, YEARFRAC will calculate the YEARFRAC using ACT/360 method. 1 pointEnter answer here. If one uses Excel's YEARFRAC(date1,date2,1) function then Excel will use the basis as per the second parameter. {. 5290896646133 On the Inventory tab enter a formula in J1 that will return the current date but not time. Close the brackets. By default, January 1,. Simple, just like when using the DATEDIF. Now let’s imagine we have a customer who has never made a contact, visit or purchase. The answer given by the formula is '1'. To calculate the age of each employee till date, the end_date is set as the date today. The World Clock – Current time all over the world. NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, WEEKNUM, WORKDAY, WORKDAY. Method 1: Using the seq() function. The DATE function returns the sequential serial number that represents a particular date. {Date},INT(YEARFRAC({Date}, TODAY())),"Who are you kidding?") system (system) Closed February 22, 2022, 12:20am 6. INT(YEARFRAC(Ep[Date],TODAY())) will retun 9 years in the above example. date. Considering the above, an Excel formula to calculate age from date of birth is as follows: YEARFRAC(date of birth, TODAY(), 1)Calculate Age from Birthdates in Years Only in Google Sheets. Countdown to Any Date – Create your own countdown. The first argument in the function is the start_date argument and the second argument is the end_date argument. Restituisce il numero di anni, sotto forma di numero intero o frazione, tra due date utilizzando la convenzione per il calcolo dei giorni specificata. =DATE(2013,9,10)+TIME(18,0,0) It will be calculate as 41527. Sounds like Excel is autoformatting something it thinks is a date, into date format, ie if I enter 1-9 in a cell, Excel automagically converts it to the 2020-01-09 date. We can type the following formula into cell C2 to do so: =INT (YEARFRAC (A2,B2)) We can then click and drag this formula down to each remaining cell in column C: Column C displays the. Note 2: Only the date portion of a date-time serial number is used by YEARFRAC. Secondly, drag down the formula to copy it to the other rows to get the years of service. I have the following problem in the YEARFRAC formula. For example the difference between 1st March 2011 and 3rd March 2012 is 1. We'll use the bare-bones formula for the YEAR function to extract the year component of date like so: =YEAR(A2) //Year From Date. Double click on the selected cell, and type this formula: =DATEDIF (cell_name, TODAY (), "Y") cell_name is the cell that contains the date of birth. Before we venture into the deeper end of the pool, let's start with some basic formulas. NET_WORKDAYS (), COUNT_DAYS () - Return number of days between 2 dates factoring in working days and holidays. how to add dates in excel automatically. Create Custom Settings. Go to cell A3. Must be a reference to a cell containing a date, a function returning a date type or a number. For example, if the participant's date of birth. An interesting result of this is that =YEARFRAC ("12/31/2016","12/31/2017") also gives you a result of 1. =YEAR (NOW ())-1978: This formula calculates the age of an. e. The formula to calculate a person’s current age is: =YEARFRAC (B2, TODAY ()), where B2 is the cell that contains that person’s date of birth. 2 Utilize ROUND Function. Problem: The date is not between January 1, 1990 and December 31, 9999How to use the Excel YEARFRAC function to return the number of years as a decimal between two dates. This is the date used to calculate the remaining years in. By the end of this module, you will be an expert in Date and Text functions. 3: - Actual number of days in month, year has 365 days. Technically, the NOW function returns the current date and time, but you can format as time only, as seen below: Note: these are volatile functions and will recalculate with every work-sheet change. DATE & TIME TOGETHER Now that we know how dates and times are stored we can put them together - ddddd. 000000 1. I selected cell E4. The result is “ 8. In the YEARFRAC formula, I have applied '0' as the basis. The YEARFRAC Function returns a decimal year difference between two dates. See also TODAY, a similar calculation that returns a date instead of a datetime. Dividing 17807 with 365 results in the number of years, 48. Move in this convention in cell D4: =YEARFRAC(TODAY(),C4,1). EOMONTH () - End of Month. Function. The World Clock – Current time all over the world. The calculator will. To use the YEARFRAC Function with Basis, you now follow the steps which have given below: Step 1: First, select Cell F5. 2. Another way to calculate the age in years in Google Sheets is by using the YEARFRAC function. Example: NOW() = 1986-03-25 1:08:21 PM: Notes: NOW does not take an argument. Now we need to find the number of remaining days. e. Other quick formulas to calculate age. From Formulas > Function, select Date & Time. C2 W2 Assessment Workbook. To count the months between the dates in cell B3 and C3, please use the formula as this: =YEARFRAC (C3,B3)*12. 14. today() years = today. This function is used to create a sequence of elements in a Vector. =YEARFRAC("1-Jan-2018","1-Jan-2019") The YEARFRAC function can be used to calculate the number of years between two dates and, will return 1, which represents one full year. It creates it using the year in cell E17, and the month in cell E17. Therefore, this function only represents the current system date, not the time. (See the last section in YEARFRAC. Click the card to flip 👆. 647058824 should appear in cell E3 which is the length of time in years. Week Number Calculator – Find the week number for any date. 2020) and. About Press Copyright Contact us Creators Advertise Developers Terms Privacy Policy & Safety How YouTube works Test new features NFL Sunday Ticket. Calculate the length of service from the hire date with YEARFRAC function. today() years = today. TODAY. TODAY Function in Excel. The difference between 1st March 2011 and 29th February 2012 is 0. =YEARFRAC(B2, TODAY(),1) B2 contains the birth date which is set as the start date. For instance, here is a list of date stored in cell B3:B5 needed to calculate the remaining days in the corresponding year, in cell C3, please use below formula: =DATE (YEAR (B3),12,31)-B3. It will return the last date of the month, i. You can use this versatile calculator to calculate the number of. Select Preferences to open the Excel Preferences dialog box. Today 2 = TODAY() Let me compare both NOW() and TODAY(). The syntax of Power BI DATEDIFF is: “DATEDIFF (, , )”. The syntax of the Excel YEAR function is as simple as it could possibly be: YEAR (serial_number) Where serial_number is any valid date of the year you want to find. The basic syntax to compute the number of years between two dates is: =DATEDIF (start_date, end_date, "y") It returns the same results as the previous formula: Now let’s count the number of rows for age ranges we define. Use the YEARFRAC and ROUNDUP function. For each function listed below, I’ll define it first, then show the function’s arguments, which are the values that functions use to perform calculations. The two functions used in this formula are the TODAY function and the YEARFRAC function. =DATEDIF (A1,B1,"y")Age=Current Year – Birth Year. LinkBack URL; About LinkBacks; Thread Tools. To get the fractional year based on the start date in cell B3 and end date in cell C3, please use below formula: =YEARFRAC (B3,C3) Press Enter key to get the result. Step 1. Learn how to use special date functions: TODAY, NOW, YEARFRAC, and DATEDIF. Returns the serial number of a particular date. =YEAR("10-Jul-2008") //Year From Date as String. [SOLVED] Date Calculations; Results 1 to 4 of 4 Date Calculations. INT(YEARFRAC(Ep[Date],TODAY())) will retun 9 years in the above example. 83333333 =YEARFRAC (A3,B3) Result: 0. The first argument in the function is the start_date argument and the second argument is the end_date argument. =YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()) The YEARFRAC function can also be used to calculate the number of years between a current date and the beginning of the year. TODAY() – It gives the current date value. By default, Excel uses the US 30/360 basis, where a year is 360 days long and each month has 30 days. 1 Apply INT Function. 8 Therefore, YEARFRAC (10/10/2000,10/10/2001) is interpreted as YEARFRAC (0. 2. Now let’s take a look at how they work for calculating years between two dates: =INT(ABS(C3-D3)/365) Firstly the ABS function processes C3-D3 and returns the number of days between the two dates as 17807. =yearfrac(date(year(today()),1,1),today()) The YEARFRAC function can also be used to calculate the number of years between a current date and the beginning of. The following formula calculates how many years old someone is: '=INT ( (B2-A2)/365)'. The Time Duration Calculator will calculate the time that has elapsed/difference between two dates with time. The following code does not require Excel. It is almost like it is treating everything as text when it evaluates everything as text for the formula to. Pages 1 This preview shows page 1 out of 1 page. You will see the number of months between the first person’s joining date and today’s date. Cool. Thus when a spreadsheet is opened, the cell in question shows the current date/time as per the TODAY() or NOW() function, but once the file is saved, the cell will. TODAY (Available in Excel 2007 and subsequent versions)Date Calculations (NOW, TODAY, YEARFRAC) Use the YEARFRAC function to work out how many years difference is there between the date 1980-07-31 (31 July 1980) and 1998-05-02 (2 May 1998) (to 1 decimal place)? In 2017 the Islamic month of fasting, Ramadan, began on Friday, 26 May. Now the formula returns 3. The TODAY() function returns today's engagement. Intermediate Skills I will expand your Excel knowledge to new horizons. The large toolbar that stretches across the top of the Excel window is known as the Excel bar. 오늘 날짜를 구해주는 today, 지금의 날짜와 시간을 구해주는 now, 요일이 뭔지 알려주는 weekday, 몇 번째 주인지 알려주는 weeknum, 그리고 기간이 1년중 차지하는 비율을 알려주는 yearfrac에 대해 알아보겠습니다. 24722222 on the end of the number. For example, the function returns '4 months' between the dates 30/9/18 and 28/2/19 (even though the 28th is the last day of the month). Date. Select the C2 cell and move the Fill Handle over the cells where you want to implement this formula. [name2] / [name_value2]: The second name, as well as its worth. The =YEARFRAC ("1/1/2017","12/31/2017") is what is less intuitive. Returns the serial number of a particular date. Next, we add the difference between the end month and start month to get the remaining months between the dates. The DAYS () function calculates the number of days. 0048 which is almost equivalent to 1 year and 2 days. 5. The age will be calculated in years, months, and days. Practice Video: Date Calculations (NOW, TODAY, YEARFRAC). 5. It is defined as: YearFrac(start_date, end_date, [basis]) Note that [basis] is the day count convention, and is specified with an integer between 0 and 4. YEARFRAC(StartDate; EndDate; Basis). e. Instant Connection to an Expert through our Excelchat Service:Here, at first, the YEARFRAC function calculates the number of years in decimal format between two dates. For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term. As a result, only whole days are returned. Now when I use YEARFRAC, it returns 11. When the date is or. Tip: You're most likely to use basis 1, unless your business uses a specific system. 005479 (1 year + 2/365 years). Use YEARFRAC(start date,end date) – calculates the number of years between 2 given dates o Use YEARFRAC(start date,today()) Date for next year: C4+365 o C4 = 1/07/2016 1/07/2017 TOOLBOX The CTRL (CMD) key = When you are using a function like CONCAT to join text from multiple cells, hold down the CTRL key while selecting the cells and Excel. 14. . Example 2 Combine DATE function. Test your Skills: Text and Date Functions • 30 minutes; Combining Text (CONCAT, &) • 30 minutes; Changing Text Case (UPPER, LOWER, PROPER) • 30 minutes; Extracting Text (LEFT, MID, RIGHT) • 30 minutes; Finding Text (FIND) • 30 minutes; Date Calculations (NOW, TODAY, YEARFRAC) • 30 minutesTo calculate the period number—i. The syntax for the TODAY function is: =TODAY () TODAY uses the computer's serial date, which stores the current date and time, as a number, as an argument. 005479 0. . To work out the fraction of a year as a decimal value, Excel uses days between two dates. {"payload":{"allShortcutsEnabled":false,"fileTree":{"C2 Excel Skills for Business Intermediate I/Quizzes":{"items":[{"name":"3D Formulas. 3. Let assume in E1 is Dec 25, 2020 (leap year) and in E1 is Jan 10, 2021 (not leap year). Search: Today's Posts: Home: Search: Today's Posts: ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Date. month == dob. ① 함수는 인수를 입력받는데, today. DAX DATE Function. For instance. - It stores as serial numbers starting at 1 for January 1, 1900. One way to find the date difference in Excel is by using arithmetic operations. Explain how it is used and when it could be used in Excel. The main function to calculate dates in Excel: DATE function Get current date and time: TODAY - returns today's date NOW - returns the current date and time. Next, hit the blue 'Calculate Years Difference' button. For example, the number of days between 2008-01-01 and 2009-01-01 is 366 (a leap year); the year between those dates has 360 days. . YEARFRAC(start_date, end_date, [basis]) The optional basis argument identifies the type of day count to use, which requires a bit of explanation: 0 or omitted US(NASD) 30/360You can get the current date with the TODAY function and the current date and time with the NOW Function. This formula subtracts the first day of the ending month (5/1/2016) from the original end date in cell E17 (5/6/2016). today(). 3 in the first instance of our example. It needs to start with a letter. Or perhaps this is days since last incident or violation. date(1980, 10, 10) def age(): today = datetime. month < dob. Formula 2: Calculate Years of Service in Years as Decimal (e. You can use YEARFRAC to do things like calculate age with a birthdate. Figure 4: Result using the Excel YEARFRAC function. [Note: The start_date, i. Here is the. 04386, how the leap or not leap shall affect the logic of above calculations? What if start date is Dec 25, 1999 and we have few leap years in the range? As a comment, formulas =(E1-D1)/365 or =YEARFRAC(D1,E1,1)I have two dates expressed as datetime objects and trying to calculate the time between the two dates in fractions of years (equivalent to the Excel yearfrac function). DATEDIFF, YEARFRAC – DAX Guide. From Formulas > Function, select Date & Time. 1. Use Microsoft VBA code. The basis is set as 1 which will give the output in terms of actual number of days in each year. 4. This formula returns the number of complete years between the birthdate and today’s date. Step 2: end_date. 10. (TODAY is another name for the DATE function. With a current date of November 24, 2020, the result from YEARFRAC for Michael Chang is: 19. Because date1 is the 1st of the month and date2 is the 31st, Excel still treats date 2 like the 31st even though 30/360 should mean that every month has 30 days. What I mean is that if DoB was 6/10/2016 and today is 6/10/2017 then yes it will give 0. For example, if DATEDIF (DATE (1969,7,16),DATE (1969,7,24),"D") returns 1/4/1900, the. , 12 months, are deducted, resulting in the date as 10th Jan 2017. Here is my code: SELECT CUSTOMER_ID, PRODUCT_SKU, SUB_START_DATE, SUB_EXP_DATE, YEARFRAC (sub_start_date, sub_exp_date) AS START_TO_END FROM. After selecting cell E5, type the formula in Formula Bar. This method of calculating age is beneficial in order to quickly subtract a date from another date. Next, refer to cell B1 where you have the ending date. For example, the fiscal year is starting from July, and to find the fiscal month of the date in cell B3, please use below formula: =CHOOSE (MONTH (B3),7,8,9,10,11,12,1,2,3,4,5,6) Press Enter key and then drag fill handle down to the cells that needed this formula. Because upgrading might impact your reports, you must upgrade date. (Source: The Spreadsheet Guru) Excel offers several built-in functions that can be used for Tombstone Date Math, including DATEDIF, TODAY, YEARFRAC, and EDATE. Online Degrees Degrees. By the end of this module, you will be an expert in Date and Text functions. To find the count of full years, use a function like AgeSimple and this expression: =AgeSimple ( [EndDate]) Share. end_date – The end date to consider in the calculation. Our end-date is today's date specified by the TODAY function, simply written TODAY(). Use the dynamic TODAY and NOW functions for real-time up-to-date calculations 9m 24s Use the FIND, MID, LEFT, and RIGHT functions Video created by 麦考瑞大学 for the course "Excel Skills for Business: Intermediate I". ans = 0. Tony was born on July 25, 1985. Instead you can change the basis: Here is the impact of changing the basis: YEARFRAC in Google Sheets. To get a whole number only (not rounded), you can use the INT function like this: = INT ( YEARFRAC (A1,B1)) // whole number only, discard decimal. By. So, when performing date calculations in Excel, you'd better supply dates using the DATE function to ensure the correct results. DaysInMonth. This method involves subtracting one date from another to calculate the difference in days. The basic syntax to compute the number of years between two dates is: =DATEDIF (start_date, end_date, "y") It returns the same results as the previous formula: Now let’s count the number of rows for age ranges we define. Here are a few Excel DATE formula examples: =DATE(2015, 5, 20) - returns a serial number corresponding to 20-May-2015. DAYS360: Returns the difference between two days based on the 360-day year used in. DAYS Function. The YEAR function in Excel returns a four-digit year corresponding to a given date as an integer from 1900 to 9999. In our example, the YEARFRAC function returns 30. =DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - returns the first day of the current year and month. The syntax of the Excel YEAR function is as simple as it could possibly be: YEAR (serial_number) Where serial_number is any valid date of the year you want to find. However, if you want the number of complete months in these time periods, you could use the formula below. さっそく下記の練習用ファイルを使用してやってみましょう！. Microsoft Excel stores date as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2018 is serial number 43101 because. Learn how to use special date functions: TODAY, NOW, YEARFRAC, and DATEDIF. Intermediate Skills I will expand your Excel knowledge to new horizons. end_date – The end date to consider in the. Syntax: length(seq(from=date_1, to=date_2, by=’year’)) -1 . 2. 647058824 should appear in cell E3 which is the length of time in years. Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. Figure 5. What I mean is that if DoB was 6/10/2016 and today is 6/10/2017 then yes it will give 0. You’ll add the birth date to cell A1 and paste the formula into B1 for the result. I am using a sumifs formula that looks at the date and sums one of the columns if the dates are between certain values. This module discusses ways you can extract information and. In this second course of our Excel specialization Excel Skills for Business you will build on the strong foundations of the Essentials course. The first one is used to calculate the total number of completed. First, enter the DATEDIF function in cell C1. Finally, in row 7, the source date is 23rd Sept 2018, to which 24 months, i. The World Clock – Current time all over the world. Age Calculation using the DATEDIF() function. The DAX Date function syntax is: DATE(year. Learn how to use special date functions: TODAY, NOW, YEARFRAC, and DATEDIF. = NOW ()-1. Look at an example: =YEARFRAC(B5,TODAY()) Note: The TODAY Function: TODAY() returns today’s date (6/13/2016 in our example) Calculate Rounded (Truncated) Age. Excel uses this “30/360” as the default method of calculating the number of years. sql-server-2008. =YEARFRAC(C3, TODAY(), 1) The decimal number can be rounded down using the INT function, =INT(YEARFRAC(C3, TODAY(), 1)) 3. 14. The Functions Arguments dialog window appears. DATE: Converts a year, month, and day into a date. Calculate the time with the SECOND, MINUTE, HOUR, and TIME functions in Excel. Replace it with your date of birth cell. Calculates the number of days, months, or years between two dates. After pressing Enter, the function will return 84722222. =YEARFRAC(B3,C3) You can add in the TRUNC or INT Functions to calculate the number of full years (just like the DATEDIF Function): =TRUNC(YEARFRAC(B3,C3)) Or you can use the ROUND Function to round to the nearest year: =ROUND(YEARFRAC(B3,C3),0) Last, you can use the ROUNDUP Function to. After putting the data of the current year (your end date) and the person’s birthdate (your start date), Microsoft Excel will give you the answer of 45. Follow the steps to calculate the age using YEARFRAC():. The Functions Arguments dialog window appears. Step 2 − The formula that is used to determine a student’s age is as follows −. today(). 99726 is actually the correct and expected result here. Learn how to use special date functions: TODAY, NOW, YEARFRAC, and DATEDIF. g.