3 Replies Latest reply: May 5, 2012 1:00 AM by Barry
leeloo44 Level 1 Level 1 (0 points)

Hi everyone, I am using numbers for the first time and I would like to now how to create automatically

a column

which starts with a precise hour and then all the following cases contain that hour plus forty minutes.

 

For example :

2:00

2:40

3:20

4:00

Thanks a lot


MacBook Pro
  • 1. Re: hours in numbers
    Barry Level 7 Level 7 (29,180 points)

    Hi leeloo,

     

    For times listing in column B, starting at B2:

     

    Start by selecting column B and setting the cell format to:

     

    Date and Time

    Date: none

    Time: 19:08 OR 7:08 PM

     

    Enter the starting time in B2.

     

    Enter the formula below in B3, then Fill down as far as you need.

     

    =B2+1/(24*3)

     

    Be aware that the value in all of these cells is a Date and Time value, and includes the date current when the first entry was made as part of the value, efvgen though it is not displayed.

     

    Regards,

    Barry

  • 2. Re: hours in numbers
    leeloo44 Level 1 Level 1 (0 points)

    Thanks Barry, this was really helpful !!!

     

    Best regards,

     

    Leeloo44

  • 3. Re: hours in numbers
    Barry Level 7 Level 7 (29,180 points)

    Thanks for the feedback, Leeloo. I was in a bit of a rush whne I posted that, and didn't have time to add the explanation below. I'm adding it now for the benefit of anyone looking for a similar solution, and wondering about the thinking behind this one,

     

    The base unit of Date and Time values is the (24 hour) Day.

    Adding 1 to a D&T value returns the D&T value one day later, at the same time of day.

    Adding 1/2 to a D&T value returns the Date and time 12 hours (one-half day) later than the start time.

     

    Leeloo wanted the time in 20 minute increments.

    20 minutes is 20/60 of an hour, or reduced to lowest terms, 1/3 of an hour.

    One hour is 1/24 of a day.

    Combining those gives 1/24 * 20/60, or 20/(24*60), or, reduced to the terms shown above, 1/(24*3).

    So the amount to be added to the previous value to give a Date and Time value 20 minutes later is 1/(24*3).

     

    Regards,

    Barry