A question regarding Numbers and adding periods of time together.

Question

Level 1

12 points

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 Feb 28, 2018 5:04 PM

Reply

Answer 1

Feb 28, 2018 6:35 PM 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:

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

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

Reply

Answer 2

SGIII

Level 8

35,252 points

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

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

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

Reply

Answer 3

Yellowbox

Level 7

20,329 points

Mar 1, 2018 6:47 AM 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).

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.

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.

Reply