Time Calculation

[Corey]

Is it possible to set up a Time Calc as below?

A1= 10:30AM
A2=10:35AM
A3=10:40AM
A4=10:45AM
A5=10:50AM
A6=10:55AM
A7=11:00AM
A8=11:05AM
A9=11:10AM
A10=11:15AM.

If i place the time in A1 and a Time in A10(45mins later) can excel formulate A2-A9 at 5 min increments to save manually entering each row time value in? How?

 

[Ron Coderre]

Try something like this:

A1: (a time)
A2: =+A1+"00:05:00"
Copy A2 down as far as you need

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Ragdyer]

May I suggest this little 'trick'?

<Tools> <AutoCorrect>
In the "Replace" box, enter 2 decimals (periods) [ .. ],
In the "With" box, enter a colon [ : ], then <OK>

Now, when times have to be entered (timecards), you can stick with using the
num keypad, with the familiar 'one-hand', 10 key touch system instead of
having to <Shift> < : > to enter times.

I hope I anticipated your goal of simplifying time entry as the reason for
this post.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Is it possible to set up a Time Calc as below?

A1= 10:30AM
A2=10:35AM
A3=10:40AM
A4=10:45AM
A5=10:50AM
A6=10:55AM
A7=11:00AM
A8=11:05AM
A9=11:10AM
A10=11:15AM.

If i place the time in A1 and a Time in A10(45mins later) can excel
formulate A2-A9 at 5 min increments to save manually entering each row time
value in? How?

 

[Corey]

Too easy.
Done the job.

Cheers.

 

[Corey]

How do enter the time then.
If i simply enter for 9:25AM;
925
I get 12:00AM
or if i enter 9.25
I get 6:00AM

???

news:[email protected]...

 

[aresen]

I don't know what you're really asking. You've had one response giving
you a formula you can enter downward to increment the times by five
minutes (don't forget the quotes). The one issue I've seen is the
"A2-A9" value to which you refer. A bit of minutia: Excel will give you
an error here because it doesn't comprehend negative time (apparently,
they don't believe that man A can do something faster than Man B).
Anyway, ABS(A2-A9) will give you fractions of a day difference if
that's what else you're may looking for. Otherwise, disregard this
reply as bable.

 

[Chip Pearson]

You need to use a VBA event procedure. You can't do it with
formatting. See www.cpearson.com/excel/datetimeentry.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

How do enter the time then.
If i simply enter for 9:25AM;
925
I get 12:00AM
or if i enter 9.25
I get 6:00AM

???

May I suggest this little 'trick'?

<Tools> <AutoCorrect>
In the "Replace" box, enter 2 decimals (periods) [ .. ],
In the "With" box, enter a colon [ : ], then <OK>

Now, when times have to be entered (timecards), you can stick
with using the
num keypad, with the familiar 'one-hand', 10 key touch system
instead of
having to <Shift> < : > to enter times.

I hope I anticipated your goal of simplifying time entry as
the reason for
this post.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all
may benefit !
---------------------------------------------------------------------------

Is it possible to set up a Time Calc as below?

A1= 10:30AM
A2=10:35AM
A3=10:40AM
A4=10:45AM
A5=10:50AM
A6=10:55AM
A7=11:00AM
A8=11:05AM
A9=11:10AM
A10=11:15AM.

If i place the time in A1 and a Time in A10(45mins later) can
excel
formulate A2-A9 at 5 min increments to save manually entering
each row time
value in? How?

 

[Ragdyer]

When you enter the time, type
9..25
And the AutoCorrect will change your entry to
9:25:00 AM

You must hit the period *twice*.

A lot easier then shifting for the colon!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

How do enter the time then.
If i simply enter for 9:25AM;
925
I get 12:00AM
or if i enter 9.25
I get 6:00AM

???

May I suggest this little 'trick'?

<Tools> <AutoCorrect>
In the "Replace" box, enter 2 decimals (periods) [ .. ],
In the "With" box, enter a colon [ : ], then <OK>

Now, when times have to be entered (timecards), you can stick with using
the
num keypad, with the familiar 'one-hand', 10 key touch system instead of
having to <Shift> < : > to enter times.

I hope I anticipated your goal of simplifying time entry as the reason for
this post.
--
Regards,

RD

--------------------------------------------------------------------------

-

Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-

Is it possible to set up a Time Calc as below?

A1= 10:30AM
A2=10:35AM
A3=10:40AM
A4=10:45AM
A5=10:50AM
A6=10:55AM
A7=11:00AM
A8=11:05AM
A9=11:10AM
A10=11:15AM.

If i place the time in A1 and a Time in A10(45mins later) can excel
formulate A2-A9 at 5 min increments to save manually entering each row
time
value in? How?

 

[Corey]

How about another task with Time Calculations.

If i have 2 cells with time values say: A1=7:30AM and B1=3:30PM.
7:30AM - 3:30PM set to NORMAL Hours
3:30PM - 6:30PM set to TIME & HALF Hours
Any time after 6:30PM - 7:29AM to be DOUBLE TIME hours

Then if i set 4 other cells for data input:
A2=( DATE 1) 29/5/2009 B2=(Date 2) 29/5/2006
A3=(Time) 7:30AM B2= (Time) 7:00PM

How can i set 3 Cells say (C1:E1) to display from the above example :
C1=8 hrs (NORMAL TIME)
D1=3hrs (Time & HALF)
E1=2hrs (DOUBLE)

This way i can input a date and time for a start and a date and a time to
finish, and get a simple breakdown of how many hours were worked and in what
pay rate category?

 

[Roger Govier]

see response to this question that you started as another thread
"How to calculate Hrs and Mins between time frames"

 

[David McRitchie]

Hi Corey,
Read the suggestion again you would type 9..25
the period is on the numeric keypad. If you set up
autocorrect as suggested the two periods together would be
converted to a colon. I think what you were expecting was
more along the lines of
http://www.cpearson.com/excel/DateTimeEntry.htm

Incidentally, if you use AM / PM then Excel expects you to
use a space after the numeric portion 9:05 AM

Back to your original posting, another way would be to
enter the first two times, Then select both of those times
and use the fill handle to fill down. Certainly have an advantage
by not generating formulas at all.
http://www.mvps.org/dmcritchie/excel/fillhand.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

How do enter the time then.
If i simply enter for 9:25AM;
925
I get 12:00AM
or if i enter 9.25
I get 6:00AM

???

May I suggest this little 'trick'?

<Tools> <AutoCorrect>
In the "Replace" box, enter 2 decimals (periods) [ .. ],
In the "With" box, enter a colon [ : ], then <OK>

Now, when times have to be entered (timecards), you can stick with using
the
num keypad, with the familiar 'one-hand', 10 key touch system instead of
having to <Shift> < : > to enter times.

I hope I anticipated your goal of simplifying time entry as the reason for
this post.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Is it possible to set up a Time Calc as below?

A1= 10:30AM
A2=10:35AM
A3=10:40AM
A4=10:45AM
A5=10:50AM
A6=10:55AM
A7=11:00AM
A8=11:05AM
A9=11:10AM
A10=11:15AM.

If i place the time in A1 and a Time in A10(45mins later) can excel
formulate A2-A9 at 5 min increments to save manually entering each row
time
value in? How?