Take a 3 or for digit time and convet it to time in one cell

[Djbaker70]

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.

 

[Héctor Miguel]

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.

 

[Djbaker70]

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

 

[Héctor Miguel]

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

 

[Djbaker70]

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

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

 

[Héctor Miguel]

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

 

[NoodNutt]

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 (e-mail address removed)

HTH
Mark.

 

[Djbaker70]

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

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

 

[Héctor Miguel]

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.