Hi,

I have entered all my times as xxxx now I want to be able to use them with a
date to calculate the difference between two dates and times so I have to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07 8:00,
the times and dates are in different columns

Thanks for your help

convert it

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jenn" wrote in message
...
Hi,

I have entered all my times as xxxx now I want to be able to use them with
a
date to calculate the difference between two dates and times so I have to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07
8:00,
the times and dates are in different columns

Thanks for your help

Hi

Thanks that worked for most of the time except I forgot to mention that
there are times that are xxx, like 800, the formula that you gave me add an
hour to these, will I have to do them separate?

Thanks

"Bob Phillips" wrote:

convert it

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jenn" wrote in message
...
Hi,

I have entered all my times as xxxx now I want to be able to use them with
a
date to calculate the difference between two dates and times so I have to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07
8:00,
the times and dates are in different columns

Thanks for your help

The easiest way that I can think of to do this is with a VBA macro with this
command line:

time_entry = mid(("" & time_entry+10000),2,2) & ":" & right(("" &
time_entry+10000),2)

The reason for adding the 10000 is to insure a "0" for times that are
earlier than 10:00. The "" & is a cheap way to convert the number into a
string. The mid() function is then used to extract the 2nd and third digits
of the number string 1xxxx, which would give you a two digit hour value in
text form. The right() function extracts the two digit minutes value.

Suppose you had these time values in cells E5 to E11 (six time values).
This macro will give you the correct results:

Sub Time_Format()
Dim R As Object
For Each R In Range("e5:e11")
R = Mid(("" & R + 10000), 2, 2) & ":" & Right(("" & R + 10000), 2)
Next R
End Sub

I tried it and it works great. Excel even thinks they are time values when
done.

If you are unfamiliar with how to create a VBA macro and then execute it,
try this:
- With Excel running, hit ALT-F11. This opens the VBA editor.
- Open up any module that has a code area (i.e. a place to write code.). If
there are no modules, use the top menu to insert a new module.
- Copy this exact code into the module (except use your own range...).
- Flip back to the open Excel worksheet and hit ALT-F8.
- Select the Time_Format macro and execute it.

I advise you save your worksheet at least once before trying this.

I hope this helps.

shjco

"jenn" wrote:

Hi,

I have entered all my times as xxxx now I want to be able to use them with a
date to calculate the difference between two dates and times so I have to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07 8:00,
the times and dates are in different columns

Thanks for your help

If you want to convert 800 to 08:00 and 1700 to 17:00 you can use this
formula

=--TEXT(A1,"00\:00")

then format the cell as hh:mm



--
Regards,

Peo Sjoblom

"jenn" wrote in message
...
Hi

Thanks that worked for most of the time except I forgot to mention that
there are times that are xxx, like 800, the formula that you gave me add
an
hour to these, will I have to do them separate?

Thanks

"Bob Phillips" wrote:

convert it

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"jenn" wrote in message
...
Hi,

I have entered all my times as xxxx now I want to be able to use them
with
a
date to calculate the difference between two dates and times so I have
to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07
8:00,
the times and dates are in different columns

Thanks for your help

Try
=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0)
--
David Biddulph

"jenn" wrote in message
...
Hi

Thanks that worked for most of the time except I forgot to mention that
there are times that are xxx, like 800, the formula that you gave me add
an
hour to these, will I have to do them separate?

"Bob Phillips" wrote:

convert it

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

"jenn" wrote in message
...
Hi,

I have entered all my times as xxxx now I want to be able to use them
with
a
date to calculate the difference between two dates and times so I have
to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07
8:00,
the times and dates are in different columns

Thanks for your help

Thanks that worked great, a couple clicks and it was all done, I was going to
go through the hundreds of records and manually enter the colon, this is much
better.

"shjco" wrote:

The easiest way that I can think of to do this is with a VBA macro with this
command line:

time_entry = mid(("" & time_entry+10000),2,2) & ":" & right(("" &
time_entry+10000),2)

The reason for adding the 10000 is to insure a "0" for times that are
earlier than 10:00. The "" & is a cheap way to convert the number into a
string. The mid() function is then used to extract the 2nd and third digits
of the number string 1xxxx, which would give you a two digit hour value in
text form. The right() function extracts the two digit minutes value.

Suppose you had these time values in cells E5 to E11 (six time values).
This macro will give you the correct results:

Sub Time_Format()
Dim R As Object
For Each R In Range("e5:e11")
R = Mid(("" & R + 10000), 2, 2) & ":" & Right(("" & R + 10000), 2)
Next R
End Sub

I tried it and it works great. Excel even thinks they are time values when
done.

If you are unfamiliar with how to create a VBA macro and then execute it,
try this:
- With Excel running, hit ALT-F11. This opens the VBA editor.
- Open up any module that has a code area (i.e. a place to write code.). If
there are no modules, use the top menu to insert a new module.
- Copy this exact code into the module (except use your own range...).
- Flip back to the open Excel worksheet and hit ALT-F8.
- Select the Time_Format macro and execute it.

I advise you save your worksheet at least once before trying this.

I hope this helps.

shjco

"jenn" wrote:

Hi,

I have entered all my times as xxxx now I want to be able to use them with a
date to calculate the difference between two dates and times so I have to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07 8:00,
the times and dates are in different columns

Thanks for your help

The easiest way I have found is to just go into format and choose custom,
then enter:

00":"00

1200 will become 12:00
800 will become 08:00
51 will become 00:51



"jenn" wrote:

Thanks that worked great, a couple clicks and it was all done, I was going to
go through the hundreds of records and manually enter the colon, this is much
better.

"shjco" wrote:

The easiest way that I can think of to do this is with a VBA macro with this
command line:

time_entry = mid(("" & time_entry+10000),2,2) & ":" & right(("" &
time_entry+10000),2)

The reason for adding the 10000 is to insure a "0" for times that are
earlier than 10:00. The "" & is a cheap way to convert the number into a
string. The mid() function is then used to extract the 2nd and third digits
of the number string 1xxxx, which would give you a two digit hour value in
text form. The right() function extracts the two digit minutes value.

Suppose you had these time values in cells E5 to E11 (six time values).
This macro will give you the correct results:

Sub Time_Format()
Dim R As Object
For Each R In Range("e5:e11")
R = Mid(("" & R + 10000), 2, 2) & ":" & Right(("" & R + 10000), 2)
Next R
End Sub

I tried it and it works great. Excel even thinks they are time values when
done.

If you are unfamiliar with how to create a VBA macro and then execute it,
try this:
- With Excel running, hit ALT-F11. This opens the VBA editor.
- Open up any module that has a code area (i.e. a place to write code.). If
there are no modules, use the top menu to insert a new module.
- Copy this exact code into the module (except use your own range...).
- Flip back to the open Excel worksheet and hit ALT-F8.
- Select the Time_Format macro and execute it.

I advise you save your worksheet at least once before trying this.

I hope this helps.

shjco

"jenn" wrote:

Hi,

I have entered all my times as xxxx now I want to be able to use them with a
date to calculate the difference between two dates and times so I have to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07 8:00,
the times and dates are in different columns

Thanks for your help

I hope you realise, Tom, that your method doesn't give you times that can be
subtracted in accordance with the OP's request. If you subtract your 00:51
from your 08:00, it would give 07:49, instead of 07:09. If you try
combining dates and times as the OP intended, things would be even more
drastically adrift.
--
David Biddulph

"TomS" wrote in message
...
The easiest way I have found is to just go into format and choose custom,
then enter:

00":"00

1200 will become 12:00
800 will become 08:00
51 will become 00:51



"jenn" wrote:

Thanks that worked great, a couple clicks and it was all done, I was
going to
go through the hundreds of records and manually enter the colon, this is
much
better.

"shjco" wrote:

The easiest way that I can think of to do this is with a VBA macro with
this
command line:

time_entry = mid(("" & time_entry+10000),2,2) & ":" & right(("" &
time_entry+10000),2)

The reason for adding the 10000 is to insure a "0" for times that are
earlier than 10:00. The "" & is a cheap way to convert the number into
a
string. The mid() function is then used to extract the 2nd and third
digits
of the number string 1xxxx, which would give you a two digit hour value
in
text form. The right() function extracts the two digit minutes value.

Suppose you had these time values in cells E5 to E11 (six time values).
This macro will give you the correct results:

Sub Time_Format()
Dim R As Object
For Each R In Range("e5:e11")
R = Mid(("" & R + 10000), 2, 2) & ":" & Right(("" & R + 10000), 2)
Next R
End Sub

I tried it and it works great. Excel even thinks they are time values
when
done.

If you are unfamiliar with how to create a VBA macro and then execute
it,
try this:
- With Excel running, hit ALT-F11. This opens the VBA editor.
- Open up any module that has a code area (i.e. a place to write
code.). If
there are no modules, use the top menu to insert a new module.
- Copy this exact code into the module (except use your own range...).
- Flip back to the open Excel worksheet and hit ALT-F8.
- Select the Time_Format macro and execute it.

I advise you save your worksheet at least once before trying this.

I hope this helps.

shjco

"jenn" wrote:

Hi,

I have entered all my times as xxxx now I want to be able to use them
with a
date to calculate the difference between two dates and times so I
have to
change all the times to xx:xx

example: 1200 needs to be 12:00 so I can go 5-jan-07 12:00 - 4-jan-07
8:00,
the times and dates are in different columns

Thanks for your help