# Thread: calculating date and time (2000)

1. ## 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. ## Re: calculating date and time (2000)

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

3. ## 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. ## 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. ## 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

6. ## 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
•