Skip navigation

hours in numbers

218 Views 3 Replies Latest reply: May 5, 2012 1:00 AM by Barry RSS
leeloo44 Calculating status...
Currently Being Moderated
May 4, 2012 11:10 AM

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
  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 4, 2012 11:38 AM (in response to leeloo44)

    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 5, 2012 1:00 AM (in response to leeloo44)

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.