Calculating elapsed time is very common whether you are managing a project or raising a baby. Elapsed time is nothing but interval between a starting point and the current point in time. We can use excel formulas to calculate elapsed time very easily.
Calculating elapsed time in years
For all the examples in this post we assume the starting date and time from which we need to calculate elapsed time is in Cell A1.
To calculate the elapsed time in years, use the formula =(NOW()-A1)/365 [Help on NOW formula]
Elapsed time in months
To calculate the elapsed time in months, we can use the formula =(NOW()-A1)/30. This returns the value in 30 day months.
Elapsed time in weeks
To calculate the elapsed time in weeks, we use the formula =(NOW()-A1)/7
Elapsed time in days
To calculate elapsed time in days, the formula is simple =TODAY()-A1. [Help on TODAY formula]
The result includes fractions as well. You can use number formatting to remove the values after decimal point.
Elapsed time in working days
To calculate elapsed time in working days, we can use the NETWORKDAYS formula like this = NETWORKDAYS(A1, TODAY()). This formula assumes 5 working days per week starting with Monday. You can also add an optional list of holidays as a parameter to it.
But if your working week is not from Monday to Friday, you can try the NETWORKINGDAYS() UDF in the same way.
Elapsed time in hours
To calculate elapsed time in hours, we can use the formula =(NOW()-A1)*24
Elapsed time in minutes
To find out elapsed time in minutes, use the formula =(NOW()-A1)*24*60
Elapsed time in seconds
In some machine critical scenarios, you might want to find the elapsed time in seconds. Just use the formula =(NOW()-A1)*24*3600
Download the Elapsed Time Worksheet and see the examples
Click here to download the elapsed time worksheet and play with the examples.
More:
Tips on using date & time in excel, List of excel date & time formulas, More excel quick tips
6 Responses to “Nest Egg Calculator using Power BI”
Wow! What a Powerful article!
Hello Chandoo Sir
your file does not work with Excel 2016.
how can I try my hands on this powerful nest egg file ?
thanks
Ravi Santwani
@Ravi... this is a Power BI workbook. You need Power BI Desktop to view it. See the below tutorial to understand what Power BI is:
https://chandoo.org/wp/introduction-to-power-bi/
As always, superb article Chandoo... 🙂
Just one minor issue:
While following your steps and replicating this calculator in PowerBI, I found that the Growth Pct Parameters should be set as "Decimal number" not "Whole Number"
OR
we have to make corresponding adjustments in the Forecast formulas (i.e. divide by 100) to get accurate results.
You are right. I used whole number but modified the auto created harvester measure with /100 at end. Sorry I did not mention it in the tutorial.
Instead of
[Growth Pct 1 Value]/12
the monthly rate has to be
(1+[Growth Pct 1 Value])^(1/12)-1
It's a slight difference but in 30 years the future value will be $100k less.