=DATEDIF(begin_date,end_date,calendar_ unit_option) Now there’s the DATEDIF date difference function that takes this form: So how do you convert an integer like 800 days into the appropriate number of years, months, and days? To get such a result in the early spreadsheet days, you had to parse out the month, day, and year components of the beginning and ending dates, and then jump through several If-test hoops to display correct results. Zee wants to express that interval in the format X years, Y months, Z days. The problem for Zee is that calculating the number of elapsed days isn’t enough. Sort your unpaid invoices in descending order by that age in days column, and you have an aged receivables report. Then, Excel would calculate the age in days by subtracting the date value entered in cell A2 from NOW(), which returns the current system date. To do so, you’d enter the following code in cell B2: Suppose the entry date or invoice date is stored in cell A2, and you want to display in cell B2 the number of days that have elapsed between that date and the current date. Zee’s date of entry is like the invoice date. Zee’s need is similar to that of business spreadsheet users who need to calculate the age in days of unpaid invoices. However, the process is identical in Excel 97, 2000, and 2002. The screen shots were captured using Excel 2000. In this brief date-math tutorial, I’ll explain how I generated the output Zee requested. I sent Zee a sample solution, and she wrote back that it was exactly what she had in mind. Using that data, Zee wanted to calculate two new columns, “length of stay in years, months, and days,” and “percent of total stay.”įortunately, Excel offers a built-in function, DATEDIF, which makes it easy to calculate elapsed calendar time. Zee, who works for a small correctional institute, had an Excel spreadsheet with the following information: This tutorial explains the solution provided by TechRepublic contributor Jeff Davis.Ī TechRepublic member we’ll call Zee wrote asking for help with date math. Teach Excel users to calculate elapsed timeĪ TechRepublic member wrote for advice on calculating the years, months, and days elapsed between two dates.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |