Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: A question regarding Numbers and adding periods of time together.

Hi, my namn is Dennis and I´ve got a sort of a weird question for you.


I´m a member of a 12 step program and I´ve been tasked to make what we call a "countdown list".
This is a list where other members write down the time thats gone by since they used any form of drug.is


Now, I wonder if it´s possible to use numbers to sum all of the participants time som that it displays the time in years, months and days.
As an example, lets say that Garry and Harry are the only two participants. Harry has 1 year and 6 months, Garry has 2 years, 8 months and 2 days. Is there a way for me to enter the numbers into a sheet and have the bottom line show me the answer, and, can I add an infinit number of partcipants in the samt sheet and have the shhet do the summing up for me?


Thanks in advance!

MacBook Pro with Retina display, OS X El Capitan (10.11.6)

Posted on

Reply

Feb 28, 2018 6:35 PM in response to Tahhec In response to Tahhec

Dennis,


Numbers has a data called a duration which may be entered in two different formats. I am going to present the format I prefer because it is less ambiguous.


when you enter a duration you can use the form:

as shown in this screenshot:

User uploaded file


so you could enter:

21w 14d


for Twenty One weeks, 14 days


NOTE: These instructions assume the names are in column A and the durations are in column B


User uploaded file

to sum a column of durations, make the last row a footer row by selecting the row (the number "10", or whatever the last row number is, to the left of the table after you select any cell). then click the triangle to select the contextual menu, then select the item "Convert to Footer Row"


in the column where you entered the durations, select the last cell of that column then type the following formula:

=sum(B)


shorthand for this is (you can copy and paste from here):

B1=sum(B)


in the cell to the right we will convert this to Years, months, days

C10=INT(DUR2DAYS(B$10)÷365)&"Y "&INT((DUR2DAYS(B$10)−INT(DUR2DAYS(B$10)÷365)×365)÷7)&"w "&INT(DUR2DAYS(B$10)−INT(DUR2DAYS(B$10)÷365)×365−INT((DUR2DAYS(B$10)−INT(DUR2DA YS(B$10)÷365)×365)÷7)×7)&"d"


you can copy and paste this formula by selecting the formula:

=INT(DUR2DAYS(B$10)÷365)&"Y "&INT((DUR2DAYS(B$10)−INT(DUR2DAYS(B$10)÷365)×365)÷7)&"w "&INT(DUR2DAYS(B$10)−INT(DUR2DAYS(B$10)÷365)×365−INT((DUR2DAYS(B$10)−INT(DUR2DA YS(B$10)÷365)×365)÷7)×7)&"d"


then select cell C10, paste

Feb 28, 2018 6:35 PM

Reply Helpful

Mar 1, 2018 6:22 AM in response to Tahhec In response to Tahhec

A simple way to track the years and months could be like this:


User uploaded file



Make the bottom row a Footer Row so it can contain formulas referring to the columns above and also so you can easily add more rows above that will be automatically included in the calculations.


The formulas:


In D2, filled down:

=B2*12+C2


In B4:

=QUOTIENT(D$4,12)


In C4:

=MOD(D$4,12)


In D4:

=SUM(D)


Once you've got it working you can hide column D.


Substitute ; for , in the formulas if your region uses , as a decimal separator.


SG

Mar 1, 2018 6:22 AM

Reply Helpful

Mar 1, 2018 6:47 AM in response to Tahhec In response to Tahhec

Hi Dennis,


Yet one more way, just to help or confuse you!

If you know the starting date for each participant, subtract it from TODAY and let Numbers do the work of calculating the Duration (days in this example).

User uploaded file

Enter the start dates in Column B


The Formula in C2 (and Fill Down)

=TODAY()


Formula in D2 (and Fill Down)

=C2−B2

to calculate the number of days.


Column E uses the DATEDIF function to display a text string.

User uploaded file

Formula in E2 (and Fill Down)

=DATEDIF(B2,C2,"Y")&"y "&DATEDIF(B2,C2,"YM")&"m "&DATEDIF(B2,C2,"MD")&"d"


Because those are text strings, you can not add them. They for display only.


Regards,

Ian.

Mar 1, 2018 6:47 AM

Reply Helpful
User profile for user: Tahhec

Question: A question regarding Numbers and adding periods of time together.