I'm trying to take just one cell and set it up to display a time. it someone
entered 330 I need the cell to display 3:30. When no time is entered in it
can display 0:00 for the time. I have tried clicking on time in the number
drop down. It shows 0:00 then I type in 930. when I hit enter it returns
0:00. If I click on the ceel it displays a date and time. All I want in the
cell is the time to show if it is entered 930. Can any one help me.

hi, !

I'm trying to take just one cell and set it up to display a time.
it someone entered 330 I need the cell to display 3:30.
When no time is entered in it can display 0:00 for the time.
I have tried clicking on time in the number drop down.
It shows 0:00 then I type in 930. when I hit enter it returns 0:00.
If I click on the ceel it displays a date and time.
All I want in the cell is the time to show if it is entered 930...

assuming you don't need to do this on the whole cells of your worksheet -?-
(i.e.) for only cells in range [B2:B5] copy/paste/type/... the following lines
in "that" worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo DoNothing
Application.EnableEvents = False
Target(1) = Format(Target(1), "0:00")
DoNothing:
Application.EnableEvents = True
End Sub

hth,
hector.

"Héctor Miguel" wrote:

hi, !

I'm trying to take just one cell and set it up to display a time.
it someone entered 330 I need the cell to display 3:30.
When no time is entered in it can display 0:00 for the time.
I have tried clicking on time in the number drop down.
It shows 0:00 then I type in 930. when I hit enter it returns 0:00.
If I click on the ceel it displays a date and time.
All I want in the cell is the time to show if it is entered 930...

assuming you don't need to do this on the whole cells of your worksheet -?-
(i.e.) for only cells in range [B2:B5] copy/paste/type/... the following lines
in "that" worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo DoNothing
Application.EnableEvents = False
Target(1) = Format(Target(1), "0:00")
DoNothing:
Application.EnableEvents = True
End Sub

hth,
hector.

Hector,
Is this a Macro that has to be made up? I don't understand what I need to
do. I did try to copy what you typed in the cell but that didn't work.
Thanks,
Dennis

hi, Dennis !

Is this a Macro that has to be made up?
I don't understand what I need to do.
I did try to copy what you typed in the cell but that didn't work...

yes, it is a vba macro-code that needs to stay in "that" worksheet code module (i.e.)

- right-click in "that" worksheet tab/name and choose: view code
- and then... copy/paste/type/... (replace whatever you see in the code-pane window)

- if your real range IS NOT as supposed (B2:B5)
change the reference in the second line of code:
from: If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo DoNothing
to: If Intersect(Target, Range("your range goes here")) Is Nothing Then GoTo DoNothing

hth,
hector.

__ OP __
I'm trying to take just one cell and set it up to display a time.
it someone entered 330 I need the cell to display 3:30.
When no time is entered in it can display 0:00 for the time.
I have tried clicking on time in the number drop down.
It shows 0:00 then I type in 930. when I hit enter it returns 0:00.
If I click on the ceel it displays a date and time.
All I want in the cell is the time to show if it is entered 930...

assuming you don't need to do this on the whole cells of your worksheet -?-
(i.e.) for only cells in range [B2:B5] copy/paste/type/... the following lines
in "that" worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo DoNothing
Application.EnableEvents = False
Target(1) = Format(Target(1), "0:00")
DoNothing:
Application.EnableEvents = True
End Sub

The formula =--TEXT(A1,"00\:00") will convert your 330 or 930 (if you format
the result as time), but it's better to type it in as 3:30 instead of 330.
--
David Biddulph

"Djbaker70" wrote in message
...
I'm trying to take just one cell and set it up to display a time. it
someone
entered 330 I need the cell to display 3:30. When no time is entered in it
can display 0:00 for the time. I have tried clicking on time in the number
drop down. It shows 0:00 then I type in 930. when I hit enter it returns
0:00. If I click on the ceel it displays a date and time. All I want in
the
cell is the time to show if it is entered 930. Can any one help me.

Hector you did great. I'm using b2 for the first time which is 1100 & then C2
for the second time which is 1900. Then I use D2 for total hours. Then I need
to Take E2 and show 8 hours of straight time and any thing over 8 hours has
to show up in F2 As over time hours. Then I had to calulate regular hour and
then over time hours by 1.5 so they can see the cost of the hours work. Can
you help. I have been trying this most of the day.
Thanks,
Dennis

"Héctor Miguel" wrote:

hi, Dennis !

Is this a Macro that has to be made up?
I don't understand what I need to do.
I did try to copy what you typed in the cell but that didn't work...

yes, it is a vba macro-code that needs to stay in "that" worksheet code module (i.e.)

- right-click in "that" worksheet tab/name and choose: view code
- and then... copy/paste/type/... (replace whatever you see in the code-pane window)

- if your real range IS NOT as supposed (B2:B5)
change the reference in the second line of code:
from: If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo DoNothing
to: If Intersect(Target, Range("your range goes here")) Is Nothing Then GoTo DoNothing

hth,
hector.

__ OP __
I'm trying to take just one cell and set it up to display a time.
it someone entered 330 I need the cell to display 3:30.
When no time is entered in it can display 0:00 for the time.
I have tried clicking on time in the number drop down.
It shows 0:00 then I type in 930. when I hit enter it returns 0:00.
If I click on the ceel it displays a date and time.
All I want in the cell is the time to show if it is entered 930...

assuming you don't need to do this on the whole cells of your worksheet -?-
(i.e.) for only cells in range [B2:B5] copy/paste/type/... the following lines
in "that" worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo DoNothing
Application.EnableEvents = False
Target(1) = Format(Target(1), "0:00")
DoNothing:
Application.EnableEvents = True
End Sub

hi, Dennis !

I'm using b2 for the first time which is 1100 & then C2 for the second
time which is 1900.
Then I use D2 for total hours.
Then I need to Take E2 and show 8 hours of straight time and any thing
over 8 hours
has to show up in F2 As over time hours.
Then I had to calulate regular hour and then over time hours by 1.5 so
they can see the cost of the hours work.
Can you help. I have been trying this most of the day...

if you need *only* B2 & C2 for the "timing entries", your 2nd line of code
should look something like...
If Intersect(Target, Range("b2:c2")) Is Nothing Then GoTo DoNothing

otherwise, if you need more rows than row2 (say row 15), your code shall be
something like:
If Intersect(Target, Range("b2:c15")) Is Nothing Then GoTo DoNothing

then you could use in D column (say D2) the following formula:
=min(c2-b2,8/24)
note that D2 needs a (custom) number format for time (i.e. h:mm)

finally, you can get overtime (i.e. in E2) with the following formula:
=c2-(b2+d2)

to perform other calculations according your needs

hth,
hector.

__ OP __
Is this a Macro that has to be made up?
I don't understand what I need to do.
I did try to copy what you typed in the cell but that didn't work...

yes, it is a vba macro-code that needs to stay in "that" worksheet code
module (i.e.)

- right-click in "that" worksheet tab/name and choose: view code
- and then... copy/paste/type/... (replace whatever you see in the
code-pane window)

- if your real range IS NOT as supposed (B2:B5)
change the reference in the second line of code:
from: If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo
DoNothing
to: If Intersect(Target, Range("your range goes here")) Is Nothing
Then GoTo DoNothing

__ OP __
I'm trying to take just one cell and set it up to display a time.
it someone entered 330 I need the cell to display 3:30.
When no time is entered in it can display 0:00 for the time.
I have tried clicking on time in the number drop down.
It shows 0:00 then I type in 930. when I hit enter it returns 0:00.
If I click on the ceel it displays a date and time.
All I want in the cell is the time to show if it is entered 930...

assuming you don't need to do this on the whole cells of your
ksheet -?-
(i.e.) for only cells in range [B2:B5] copy/paste/type/... the
following lines
in "that" worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo DoNothing
Application.EnableEvents = False
Target(1) = Format(Target(1), "0:00")
DoNothing:
Application.EnableEvents = True
End Sub

G'day Dennis

I have an example file that I did for someone else that calculates overtime
and even does tax calculations.

If you like I will send it to you so you can have a play with it.

We can do this 2 ways:

1. reply with your e-mail address
2. drop me a line at

HTH
Mark.

Hector,
Everything looks better. The only problem left is. When I convert the hour
and the Overtime. It shows in hours like 8:00 and over time as 4:30. What I
need them to do is show as 8 for the hours and over time hour should like
like this 4.5. I have added the two formulas from the two Cells This is E2
=MIN(C2-B2,8/24) and this one is F2 =C2-(B2+E2) I hope this helps. The
times are Start time 6:30 in B2 and End Time is 19:00 in C2. I hope this
helps.
Thanks,
Dennis
"Héctor Miguel" wrote
hi, Dennis !

I'm using b2 for the first time which is 1100 & then C2 for the second
time which is 1900.
Then I use D2 for total hours.
Then I need to Take E2 and show 8 hours of straight time and any thing
over 8 hours
has to show up in F2 As over time hours.
Then I had to calulate regular hour and then over time hours by 1.5 so
they can see the cost of the hours work.
Can you help. I have been trying this most of the day...

if you need *only* B2 & C2 for the "timing entries", your 2nd line of code
should look something like...
If Intersect(Target, Range("b2:c2")) Is Nothing Then GoTo DoNothing

otherwise, if you need more rows than row2 (say row 15), your code shall be
something like:
If Intersect(Target, Range("b2:c15")) Is Nothing Then GoTo DoNothing

then you could use in D column (say D2) the following formula:
=min(c2-b2,8/24)
note that D2 needs a (custom) number format for time (i.e. h:mm)

finally, you can get overtime (i.e. in E2) with the following formula:
=c2-(b2+d2)

to perform other calculations according your needs

hth,
hector.

__ OP __
Is this a Macro that has to be made up?
I don't understand what I need to do.
I did try to copy what you typed in the cell but that didn't work...

yes, it is a vba macro-code that needs to stay in "that" worksheet code
module (i.e.)

- right-click in "that" worksheet tab/name and choose: view code
- and then... copy/paste/type/... (replace whatever you see in the
code-pane window)

- if your real range IS NOT as supposed (B2:B5)
change the reference in the second line of code:
from: If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo
DoNothing
to: If Intersect(Target, Range("your range goes here")) Is Nothing
Then GoTo DoNothing

__ OP __
I'm trying to take just one cell and set it up to display a time.
it someone entered 330 I need the cell to display 3:30.
When no time is entered in it can display 0:00 for the time.
I have tried clicking on time in the number drop down.
It shows 0:00 then I type in 930. when I hit enter it returns 0:00.
If I click on the ceel it displays a date and time.
All I want in the cell is the time to show if it is entered 930...

assuming you don't need to do this on the whole cells of your
ksheet -?-
(i.e.) for only cells in range [B2:B5] copy/paste/type/... the
following lines
in "that" worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("b2:b5")) Is Nothing Then GoTo DoNothing
Application.EnableEvents = False
Target(1) = Format(Target(1), "0:00")
DoNothing:
Application.EnableEvents = True
End Sub

hi, Dennis !

The only problem left is. When I convert the hour and the Overtime.
It shows in hours like 8:00 and over time as 4:30.
What I need them to do is show as 8 for the hours and over time hour should like this 4.5.
I have added the two formulas from the two Cells This is E2 =MIN(C2-B2,8/24)
and this one is F2 =C2-(B2+E2) I hope this helps.
The times are Start time 6:30 in B2 and End Time is 19:00 in C2. I hope this helps...

in excel, dates & time are just serial numbers (integers=date / fractions=time)

if you need calculations performed in decimal notation
the factor is 24 (hours a day) so, try with:

[E2] =min(8,(c2-b2)*24)
[F2] =(c2-b2)*24-e2

hth,
hector.