Days in Excel formulas — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily

Days in Excel formulas

Get PDF file

by on
in Excel Training,Office Technology

Q. I need to prorate mileage over the number of days an employee was accident free. What would that formula look like?

A. Well, it’s actually a couple of formulas. First you need to know the number of days they were accident free. To accommodate a possible leap year, you may want to use start and end dates as I have in the green- and orange-shaded cells. The end-of-the-year date minus the accident date would yield the new number of days accident free (D1-B4). To be precise you may want to add 1.

Now that we know that, we can divide the total miles driven for the year by the number of days in the year. While you could use 365, or 360, if you want to be precise, subtract the start date for the year (green) from the end date for the year (orange).

Days in formulas

Now, multiply that by the number of accident-free days. Use an absolute cell reference for the start and end dates to make sure they don’t “travel” as you copy them down ($B$1, $D$1).

Leave a Comment

Previous post:

Next post: