Adding minutes to a Date/Time in Excel

[iSeriesCodePoet]

We are planning an upgrade on a finacial systems and we are determining some timings of things. I can convert from minutes to days/hours/minutes easily using some simple mathmatics. But I can't figure out how to take a starting date and time and then add on these minutes to the date that we select. Here is an example

I have a cell with 7/23/2005 17:00. On a couple rows I have 5, 50, 5, 120, etc. So on the first row, I want the result to be 7/23/2005 17:05, on the next 7/23/2005 17:55, the next 7/23/2005 18:00 and so on. Does anyone have any ideas? I looked through the Excel funtions and couldn't what I think I am looking for.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

http://www.spreadfirefox.com/?q=affiliates&id=2483&t=1

 

[sawedoff]

1 minute is .000694 of a day. (1/(24*60).

Add .000694 times how many minutes you want to add.

You may have to reformat, but that should take care of your problem.

Sawedoff

 

[AnotherHiggins]

Excel stores every date as a whole number. Today is 38432 - that's 38,432 days since 1/1/1900.

Excel stores times as a decimal - a percentage of 24 hours. 6AM is .25, noon is .5. it is now 16:08:44 - that works out to .672731.

The only trick to making Excel do arithmetic with times is to make it understand that you are entering times.

Entering [COLOR=blue white]7/23/2005 17:00[/color] into a cell will work just fine. Excel knows that's a time.

However, entering 5 into a cell and adding it to the first cell causes Excel to assume you meant 5 days.

So all you need to do is enter 5 minutes as [COLOR=blue white]0:0:05[/color]. Excel will realize that you mean 5 minutes. [COLOR=blue white]0:0:50[/color] will be recognized as 50 minutes.


[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.

 

[TonyJollans]

Hi iSeriesCodePoet,

Let's say you have your base time, 7/23/2005 17:00, in cell A1.

And in B1 you have 5
And in B2 you have 50
And in B3 you have 120
etc.

In C1, Enter [blue][tt]=$A$1+TIME(0,B1,0)[/tt][/blue]
And copy this down column C as far as you have minutes in column B. Format as Date if it doesn't do it automatically.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[