3 Replies Latest reply: Jan 18, 2009 5:17 AM by HeidiSAF
HeidiSAF Level 1 (0 points)
I am wondering if there is a way to add or subtract from an hour of day. I am a wedding planner and would like to make a template for wedding timeline. For example, 5:00 PM is the ceremony, + 30 minutes from ceremony start time, cocktail reception begins, + 1.5 hours from ceremony start time, guests invited to dinner. Trying to streamline my business to make it more efficient.

Thanks in advance for your help, and I apologize for my ignorance!

MacBook, Mac OS X (10.5.2)
  • Barry Level 7 (29,610 points)
    Hi Heidi,

    There may be a simpler solution, but this seems to work.


    Enter the following in the cells named:

    B2: 1:00 PM
    B3: 1:01 PM
    B4: =B2-B1

    This will give you a number in B4 that is the fraction of a 24 hour day equivalent to 1 minute.


    Put your base time ( 5:00 PM) in B5.

    For the example, I wanted the schedule times in column E, starting at E7, and I used Column D to carry the increment in minutes from the base time.

    E7: =B$5+(B$4*D7)

    Click the checkmark to confirm the formula, then drag the handle at the lower right of the cell to fill the formula down to as many rows as you need schedule items. (All cells should show 5:00 PM as you fill them.)

    Enter 30 in D8 to change the time in E8 to 30 minutes after the base time, etc.

    Note: While this worked for ADDING time to a base time, when I changed the + to a - the result cell displayed the base time no matter what value was in column D.

  • jaxjason Level 4 (3,535 points)
    I would seriously suggest downloading and trying out version '09. It has a duration data type that works perfectly for this.

    otherwise I would make one column Date/time with no date, only time shown. Then another column for the "number of minutes". Formatted to numbers. You would type in the number f minutes, 30, 45 90 etc... for the duration of that step. The start time for the next step is calculated.

    See the below picture for what I did:

    Hope this helps,

    Message was edited by: jaxjason
  • HeidiSAF Level 1 (0 points)
    Thanks, I appreciate the input, and I eventually will get IWork '09, but for the time being, this helped!