How do you Increment Time??

[Crackles McFarly]

I'm using, and love it, Excel 97. I want to know how to increment
TIME.

Say I have a few rows, each start at 10:00 AM and I want each to
increment at different intervals. Let's say row 1 is 15 min, row 2 is
17 minutes, row 3 is 60 minutes, row 4 is 75 minutes, etc..etc..

I cannot find documentation about this within Excel NOR on Microsoft's
web site.

Does anyone of you know how to do this? I'd sure appreciate the help.
thanks,

 

[Guest]

In B1 enter:

=TIME(HOUR(A1),MINUTE(A1)+15,0) and copy across

The 15 is for 15 minutes. Change the interval to suit your needs.

 

[Dave Peterson]

=a1+time(0,15,0)
would add 15 minutes to the time in A1.

 

[Crackles McFarly]

In B1 enter:

=TIME(HOUR(A1),MINUTE(A1)+15,0) and copy across

The 15 is for 15 minutes. Change the interval to suit your needs.
--

OMG THANK YOU!!!!

That was so simple, you'd think MSFT would include this as an easy
find.

Made my day man...

 

[Crackles McFarly]

=a1+time(0,15,0)
would add 15 minutes to the time in A1.

That appears to give the result of 1 Hour and 15 Mins...

?

 

[Dave Peterson]

If you have 1 hour in A1, then yep--otherwise, nope.

 

[Crackles McFarly]

Dave, which EXCEL newsgroups is most useful, most answers?

I'll try to limit myself to it..

thanks..

 

[Gord Dibben]

Most regulars monitor all the Excel news groups so one post to any news group
will get picked up.

For VBA programming questions the programming news group is best but for all
other types of questions the Exce.misc group gets by far the most traffic.


Gord Dibben MS Excel MVP

 

[Wild Bill]

=a1+time(0,15,0)
would add 15 minutes to the time in A1

Dave that's an intriguing solution. Interestingly, DATE falls apart,
easily seen with 1/1/2007 in A1 or =A1+DATE(0,0,3) or =A1+DATE(0,3,0)
and =A1+DATE(3,0,0).

The problem of course is that TIME is 0-based and DATE is 1900-based.
Well, you can add days with =A1+DATE(1900,1,X) for positive X. For
adding years I've tried solutions like =A1+DATE(19+X,1,0) but leap side
effects cause it to need a more complicated formula. And to add months,
I see no light at the end of this approach.

Of course, I know the "right" way of e.g.
date(year(A1)+X,month(a1),day(a1)) for the year case (and similarly for
the other two), but your answer prompted my curiosity. I'm sure you'll
say, yours is a TIME trick, not a DATE one. Too bad, because your TIME
trick is pleasantly more compact than
time(hour(A1)+X,minute(a1),second(a1)).

P.S. All of this is based on Excel being on the 1900 date system.

 

[Dave Peterson]

And just to be different from Gord...

I'd say that if you want a worksheet formula solution, then I'd recommend
posting in .worksheet.functions.

But in any case, pick one group and post a single message.

If you think you hve to post to multiple groups, then cross-post your message
(like you did in other groups).

I don't think that this is ever necessary when you're asking an excel question.
It may be worth asking if your question spans multiple disciplines--maybe
include an MSWord or PowerPoint or whatever in your headers.

 

[Dave Peterson]

But I wouldn't call them tricks. I think that's more of a description on how
both =time() and =date() work.

 

[Wild Bill]

But I wouldn't call them tricks. I think that's more of a description on how

both =time() and =date() work.

Point taken. But I refuse to back off of "clever" <g> - evidenced by
the number of "mortals" that do it the long way. Like I USED to - now
Add one more to the hundreds of optimizations I've grabbed here.

BTW I was careless with "and" and "or" in my post 2nd paragraph - minor
point.

 

[Dave Peterson]

I'm not sure if I understood if you haven't tried this:

=date(year(a1),month(a1)+7,day(a1))

Or that you just find this clumsy.

I'm betting that you just found it clumsy.

 

[Wild Bill]

I'm not sure if I understood if you haven't tried this:

=date(year(a1),month(a1)+7,day(a1))

Or that you just find this clumsy.

Exactly, but further, I was grasping for an analog to the enormous
optimization you achieve with =a1+time(0,7,0) vs.
=time(hour(a1),minute(a1)+7,second(a1)). Intriguing enough to try!

Thanks for these and all of your unrepayably valuable contributions.

 

[Sandy Mann]

Dave that's an intriguing solution. Interestingly, DATE falls apart,
easily seen with 1/1/2007 in A1 or =A1+DATE(0,0,3) or =A1+DATE(0,3,0)
and =A1+DATE(3,0,0).

The problem of course is that TIME is 0-based and DATE is 1900-based.

I don't think that it *falls apart* so much as it does not do what you think
that it is doing.

The problem with =A1+DATE(0,0,3) is that while there is a YEAR zero and a
DAY zero there was no MONTH zero in that year. The *zero* starting point is
=DATE(0,1,0) not =DATE(0,0,0). =DATE(0,0,3) would therefore be a negative
number which in the 1900 date system is illegal. =Month(0) is the month of
DAY zero not the month of YEAR zero. (DATE(1,0,3) is 1900-12-3 in the
internationa date system so if follows that =DATE(0,0,3) would be one year
before that presumably 1899-12-3)

=A1+DATE(0,3,0) and =A1+DATE(3,0,0) do what I would expect them to do:

=DATE(0,3,1) is 1900-3-1
=DATE(0,3,0) is therefore the day before that which is 1900-2-29
(because XL *thinks* the the year 1900 was a leap year)

It follows then that with 2007-8-20 in A1 then:
=A1+DATE(0,3,0) returns 2007-10-19 and
=A1+DATE(0,2,0) returns 200-9-20

Why the difference of a day? Because +DATE(0,3,0) is *NOT* adding three
months but is adding 60 DAYS because =DATE(0,3,0) is 1900-2-29 which is day
60. The date that you end up with will therefore depend on the number of
days that XL *thinks* were in that number of months in the year 1900 and the
number of days in the start month and consequent months.

The same principle applies to years.

You cannot successfully add months or years to a date and end up on the same
day of the month.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Wild Bill]

That's very smart, Sandy. Thank you for explaining what I said. BTW, you
might check another feed to see if you're missing parts of the thread.

 

[Barn E. Fife]

The *zero* starting point is =DATE(0,1,0) not =DATE(0,0,0).
Actually, it is DATE(1900,1,0). The 1904 system is like neither:
=DATE(1904,1,1).

You cannot successfully add months or years to a date and end up on the same day of the month.

You can, but as a practical matter it's some work.
O.P. suggested that you can _almost_ do so with years, except for leap
issues. (The asymmetry of months is another matter, so they're off the
table.) When he said =A1+DATE(19+X,1,0) I'm certain he meant
=A1+DATE(1900+X,1,0). That's actually not a bad stab; that formula is
correct (for nonnegative x) for A1 in the year immediately preceding
each leap day, but "falls apart" for other dates. "non leap day" in
2100(etc.) also must be addressed.

I wouldn't recommend adding years this way because further manual leap
adjustment logic would be required (as also is the case with the 1904
system's counterpart =A1+DATE(1904+X,1,1)). But aside from some leap
adjustments, it can be done. Speak up if you still don't understand
why.

 

[Sandy Mann]

That's very smart
Just thinking out loud really.


=A1+DATE(0,7,0)
Looks as if it should add 7 months on to the date in A1 but ironically
=A1+DATE(0,8,0) comes closer to adding seven months.

+DATE(0,7,0) adds a constant 182 days and +DATE(0,8,0) adds a conatant 213
days

Dave's formula:

=date(year(a1),month(a1)+7,day(a1))

does add 7 months which is between 212 and 215 days depending on the time of
year.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Actually, it is DATE(1900,1,0).

Technically I agree but I felt that as Wild Bill had used *DATE(0,* I felt
that it was better to use his format in my explanation. DATE(1900,1,0) and
DATE(0,1,0) both return zero of course.

(The asymmetry of months is another matter, so they're off the
table.)

Agreed. That is the main thrust of my argument.

=A1+DATE(1900+X,1,0). That's actually not a bad stab; that formula is
correct (for nonnegative x) for A1 in the year immediately preceding
each leap day.

Only for months March - December but is this any more helpful than the fact
that =A1+DATE(1900,8,0) correctly adds 7 (not 8) months to an August -
December date in the year preceding a leap year?

So perhaps I should have said: *except for in the year preceding a leap
year, and in the periods March to December, for years or August to December
for months, you cannot successfully add years or months as appropriate to a
date and end up on the same day of the month* but is it worth it?



--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Barn E. Fife]

"The year preceding" doesn't mean March-December - think about it.

In truth he's already got the formula for one-fourth of the cases and
the footprint for the rest. It's not exactly the frivolous scenario
you expressed.

AAR it's moot; he said he was just speculating on finding a strong
optimization like TIME has. Nonetheless, summarily pooh-poohing ideas
with potential should remain the province of governments and Microsoft
design meetings, and I refuse to cave in to that bad karma! Just my
opinion!!