Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculating date and time (2000)

    Hi,

    I'm trying to create a spreadsheet that will help me to generate date and time automatically based on a set of time and date input.
    Eg. when I enter 23/9/05 20:30pm, the sheet will help to generate a day say 3 days later, eg. 26/9/05 20:29pm. Is there a way I can do that?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating date and time (2000)

    Do you really want a date/time 3 days minus 1 minute later?

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating date and time (2000)

    3 days minus 1 minute later is just a possible scenario. Essentially I would like to know how to come up with a formula that will allow me to add to a set of (date:time) data to generate a set of (date:time) based on my input. Like I might want the generated day:time to be 5 days later, and 30 minutes earlier. Is that possible without using macros?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating date and time (2000)

    Say that you enter a date/time in A1.
    A possible formula in B1 (or wherever you want it) to calculate a date/time 3 days minus 15 minutes later is
    =A1+3-TIME(0,15,0)
    (The TIME function takes 3 arguments: hours, minutes, seconds)
    Alternatively, since there are 24*60 = 1440 minutes in a day:
    =A1+3-15/1440
    You can also place the number of days to be added in a cell, say A2, and the number of minutes to be added in another cell, say A3. To subtract, enter a negative number. The formula would become:
    =A1+A2+A3/1440
    You can easily devise variants of this.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: calculating date and time (2000)

    Just a word of clairification on Hans's post. Excel keeps dates as floating-point numbers with days as the whole number and time as the decimal portion representing parts of a day. So, the 1440 in Hans's formula is 24*60, 24 hours in a day and 60 minutes in an hour. So, 1 munute is 1/1440 of a day. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    New Lounger
    Join Date
    Apr 2002
    Location
    Singapore
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating date and time (2000)

    Thanks for your help! It's working great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •