Using a duration cell in another formula

Question

Level 1

0 points

Using a duration cell in another formula

I'm trying to get a moving percentage that shows a daily update of what the percentage of completion is for a 12 month period. I entered the starting date in one cell, and the DATE function in the next cell, then the DURATION formula in the third cell, showing the answer in days. When I try to do the simple formula for percentage (and yes, the cell is formatted correctly) by taking the amount in the DURATION cell and dividing it by 366, I get either nothing--no formula recorded in the cell, or something that looks like a time answer 1:02:45.3 or something like that.

Soooo, can I use a DURATION formatted cell in another formula and, if so, how?

Thanks!

Macbook Pro, Mac OS X (10.6.6)

Posted on Dec 17, 2015 1:41 PM

Reply

Answer 1

Yellowbox

Level 7

21,945 points

Dec 19, 2015 3:16 AM in response to pbrinck52

Hi pbrinck,

I am not sure what you want to do. Maybe this will give you something to work on.

Start Date in B1 (typed in)

Formula in B2 =TODAY()

Formula in B3 ( showing 352 days) =B2−B1

Formula in B4 (and B4 is formatted as Percentage, Decimals 0) =DUR2DAYS(B3)÷366

Please call back with questions.

Regards,

Ian.

Reply

Answer 2

pbrinck52 Author

Level 1

0 points

Dec 19, 2015 4:30 AM in response to Yellowbox

Hey, Ian. Thanks for your time and help. You are spot on, your formulas are exactly what I used--and I can't get a percentage out of the final cell. Instead, I get a time format.

12/1/15

12/19/15

18

1:10:49.180

12/1 is the starting date, 12/19 is today, 18 is the number of days found with the same formula you used (a simple subtraction formatted in days) and then the final cell is just the previous cell/366. The cell automatically formats itself in "DURATION" and cannot be changed to a percentage, no matter how many ways I try to do it.

Thanks,

Peter

Reply

Answer 3

Yellowbox

Level 7

21,945 points

Dec 19, 2015 5:32 AM in response to pbrinck52

Hi Peter,

Here we go with long date format (weekday, monthday, month, year) to avoid misunderstanding 😉).

Start Date (A2) is typed in.

Formula in B2 =TODAY() (Yes, we in Australia have gone past midnight, so it is 20 December local time).

Formula in C2 =B2−A2 (just for illustration; you can combine this into a more complex formula)

C2 is Formatted as

Formula in D2 =DUR2DAYS(C2)

Formula in E2 =D2÷366

Sorry for breaking this down into small steps, but my brain works best that way.

One thing that puzzles me: why are you using 366 days? 2015 is not a Leap Year 👿.

Regards,

Ian.

Reply

Answer 4

pbrinck52 Author

Level 1

0 points

Dec 19, 2015 5:58 AM in response to Yellowbox

Ah, THAT's the key--didn't know about the DUR2DAY formula. Many thanks.

And, appreciate the help from "tomorrow"---we've been to your amazing country many times, always loved it and its people.

As to the 366, the spreadsheet is showing the percentage of 12 months beginning 12/1/15 through 11/30/16.

Be well, and thanks again.

Peter

Reply

Answer 5

Yellowbox

Level 7

21,945 points

Dec 19, 2015 6:22 AM in response to pbrinck52

Hi Peter,

Now I see!

As to the 366, the spreadsheet is showing the percentage of 12 months beginning 12/1/15 through 11/30/16.

Perhaps you could use the DATEDIF function to calculate the difference (in days) between the start and end dates. Use that as the base for calculating % of the year.

From the Function Browser (type = in any cell then type datedif in the search box)

The DATEDIF function returns the number of days, months, or years between two dates.

DATEDIF(start-date, end-date, calc-method)

start-date: The starting date. start-date is a date/time value (the time portion is ignored) or date string value.
end-date: The ending date. end-date is a date/time value (the time portion is ignored) or date string value.
calc-method: A modal value that specifies how to express the time difference and how dates in different years or months are handled.
- “D”: Return the number of days between the start and end dates.
- “M”: Return the number of months between the start and end dates.
- “Y”: Return the number of years between the start and end dates.
- “MD”: Return the days between the start and end dates, ignoring months and years. The month in end-date is considered to be the month in start-date. If the starting day is after the ending day, the count starts from the ending day as if it were in the preceding month. The year of the end-date is used to check for a leap year.
- “YM”: Return the number of whole months between the start and end dates, ignoring the year. If the starting month/day is before the ending month/day, the dates are treated as though they are in the same year. If the starting month/day is after the ending month/day, the dates are treated as though they are in consecutive years.
- “YD”: Return the number of days between the start and end dates, ignoring the year. If the starting month/day is before the ending month/day, the dates are treated as though they are in the same year. If the starting month/day is after the ending month/day, the dates are treated as though they are in consecutive years.

Example, using DATEDIF to calculate someone's age:

Greetings from Downunder!

Regards,

Ian.

Reply