MacMost Q&A Forum • View All Forum QuestionsAsk a Question

# How Do I Create a Moveable Date Value In Numbers?

I am trying to create a spreadsheet that tracks the number of months a client has been with my company. Both past and current.
For example, if John started with me on January 1st and left on July 1st. The first column would show his start date, the next column would show his leaving date. The final column would show the number of months he was with me. I get how to do this.
But how do I track the clients that are still currently with me? For example if John who started on January 1st is still current. Each month the cell value would update the number of months John is with me. What formula or function do I use for this?
—–
David

### Comments: 3 Responses to “How Do I Create a Moveable Date Value In Numbers?”

6 years ago

Try the TODAY function for the date. What I would do is to use an IF function to check the value of the end date column cell. If it is empty, then calculate the duration using the start date and TODAY. If the cell is not empty, then calculate it with the start date and end date cells.

David
6 years ago

That worked.
Question though. The cell that is totaling the duration is a negative number.
For example I put 1/1/17 to 6/30/17 and the total is -26 weeks.
Two questions:
1. How do I change it to a positive duration?
2. Is there a way to make the duration in months not weeks?

6 years ago

You never mentioned how you are calculating the duration, but it sounds like you are doing it backwards, taking 1/1/17 and subtracting 6/30/17. Which would be -26 weeks. So do it the other way, reversing your formula.
As for months, this is tricky, as what is a month? 30 days? The exact month? How you do it depends on what you want. Do you round down or up or to the nearest value too? If you use the YEAR function and the MONTH function and convert each date to a month and a year, and then find the difference of each, multiply the difference of the years by 12, you get a very long formula that still returns 0 for 1/1/17 to 1/31/17 but 1 for 1/31/17 to 2/1/17, which is probably not what you want.
That's why duration formatting is in days and weeks, but not months since months is a vague measurement.