Time is locked to General format
 

Hello,
I am a workforce analyst, and needs to know how many hours of lates, time
off, OT's, etc..we have...so I created a worksheet.

I exported the entered Hours from our database but unfortunately it is in
General Format and seems to be locked. When multiplying it to 24 to convert,
it gives #VALUE error data is of different type. I tried the TIMEVALUE
function to unlock but it does not show the correct number of hours if it
goes beyond 24.

Example: Total OT hours is 31 hours (31:00 display from exported database),
the format is General and it can't be changed...I used the TIMEVALUE function
to "unlock" then multiplied it by 24, it gives 7 Hours only when changed to
number format, I realized it lacks 24 hours so I used the [h]:mm (bracket)
format but still does not include all hours.

I don't have issue if the collected hours is lower than 24 hours.
Example, collected hours is 6:30 using TIMEVALUE and multiply by 24 gives
6.5 hours in number format this is good but when collected hours goes beyond
24, ther goes the problem.

Ultimately what I want to see is...
Example: Collected Hours is 435:15 (display from exported database, in
General "locked" format)

Result should be 435.25 hours

Your help is greatly appreciated.

Thanks!

Time is locked to General format
 

There might be unseen characters like leading/trailing spaces:

<space31:00
31:00<space
<space31:00<space

If you have just that one cell try re-entering the value. If you a bunch of
cells try:

Select the range of cells in question
Got the menu EditReplace
Find what: hit the space bar
Replace with: nothing, leave this empty
Replace all

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hello,
I am a workforce analyst, and needs to know how many hours of lates, time
off, OT's, etc..we have...so I created a worksheet.

I exported the entered Hours from our database but unfortunately it is in
General Format and seems to be locked. When multiplying it to 24 to
convert,
it gives #VALUE error data is of different type. I tried the TIMEVALUE
function to unlock but it does not show the correct number of hours if it
goes beyond 24.

Example: Total OT hours is 31 hours (31:00 display from exported
database),
the format is General and it can't be changed...I used the TIMEVALUE
function
to "unlock" then multiplied it by 24, it gives 7 Hours only when changed
to
number format, I realized it lacks 24 hours so I used the [h]:mm (bracket)
format but still does not include all hours.

I don't have issue if the collected hours is lower than 24 hours.
Example, collected hours is 6:30 using TIMEVALUE and multiply by 24 gives
6.5 hours in number format this is good but when collected hours goes
beyond
24, ther goes the problem.

Ultimately what I want to see is...
Example: Collected Hours is 435:15 (display from exported database, in
General "locked" format)

Result should be 435.25 hours

Your help is greatly appreciated.

Thanks!

Time is locked to General format
 

Hi there,

Thank you very much!!! yes there is a bunch of cells that I need to enter.

Only those numbers with thousand hours are not changed example: 2400:30,

It's more likely that i will encounter hours (in thousand) entered as I will
need Month to date reports.

All others (hours in hundreds, ex. 324:25) are fixed!!

I hope you can show me how to fixed those thousands :)

Again, thanks for you help and time.

"T. Valko" wrote:

There might be unseen characters like leading/trailing spaces:

<space31:00
31:00<space
<space31:00<space

If you have just that one cell try re-entering the value. If you a bunch of
cells try:

Select the range of cells in question
Got the menu EditReplace
Find what: hit the space bar
Replace with: nothing, leave this empty
Replace all

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hello,
I am a workforce analyst, and needs to know how many hours of lates, time
off, OT's, etc..we have...so I created a worksheet.

I exported the entered Hours from our database but unfortunately it is in
General Format and seems to be locked. When multiplying it to 24 to
convert,
it gives #VALUE error data is of different type. I tried the TIMEVALUE
function to unlock but it does not show the correct number of hours if it
goes beyond 24.

Example: Total OT hours is 31 hours (31:00 display from exported
database),
the format is General and it can't be changed...I used the TIMEVALUE
function
to "unlock" then multiplied it by 24, it gives 7 Hours only when changed
to
number format, I realized it lacks 24 hours so I used the [h]:mm (bracket)
format but still does not include all hours.

I don't have issue if the collected hours is lower than 24 hours.
Example, collected hours is 6:30 using TIMEVALUE and multiply by 24 gives
6.5 hours in number format this is good but when collected hours goes
beyond
24, ther goes the problem.

Ultimately what I want to see is...
Example: Collected Hours is 435:15 (display from exported database, in
General "locked" format)

Result should be 435.25 hours

Your help is greatly appreciated.

Thanks!

Time is locked to General format
 

Hmmm...

You can enter times up to 9999:99:99.

There maybe some other unseen "junk" in there.

There is a macro at this site that will clean "junk" from your data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

I use this macro every time I import or copy from a website.

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hi there,

Thank you very much!!! yes there is a bunch of cells that I need to enter.

Only those numbers with thousand hours are not changed example: 2400:30,

It's more likely that i will encounter hours (in thousand) entered as I
will
need Month to date reports.

All others (hours in hundreds, ex. 324:25) are fixed!!

I hope you can show me how to fixed those thousands :)

Again, thanks for you help and time.

"T. Valko" wrote:

There might be unseen characters like leading/trailing spaces:

<space31:00
31:00<space
<space31:00<space

If you have just that one cell try re-entering the value. If you a bunch
of
cells try:

Select the range of cells in question
Got the menu EditReplace
Find what: hit the space bar
Replace with: nothing, leave this empty
Replace all

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hello,
I am a workforce analyst, and needs to know how many hours of lates,
time
off, OT's, etc..we have...so I created a worksheet.

I exported the entered Hours from our database but unfortunately it is
in
General Format and seems to be locked. When multiplying it to 24 to
convert,
it gives #VALUE error data is of different type. I tried the TIMEVALUE
function to unlock but it does not show the correct number of hours if
it
goes beyond 24.

Example: Total OT hours is 31 hours (31:00 display from exported
database),
the format is General and it can't be changed...I used the TIMEVALUE
function
to "unlock" then multiplied it by 24, it gives 7 Hours only when
changed
to
number format, I realized it lacks 24 hours so I used the [h]:mm
(bracket)
format but still does not include all hours.

I don't have issue if the collected hours is lower than 24 hours.
Example, collected hours is 6:30 using TIMEVALUE and multiply by 24
gives
6.5 hours in number format this is good but when collected hours goes
beyond
24, ther goes the problem.

Ultimately what I want to see is...
Example: Collected Hours is 435:15 (display from exported database, in
General "locked" format)

Result should be 435.25 hours

Your help is greatly appreciated.

Thanks!

Time is locked to General format
 

Hi,

Thank you very for your help. This worked! I don't know macro yet, but this
one is a great start! I should start looking into macro eh?

"T. Valko" wrote:

Hmmm...

You can enter times up to 9999:99:99.

There maybe some other unseen "junk" in there.

There is a macro at this site that will clean "junk" from your data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

I use this macro every time I import or copy from a website.

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hi there,

Thank you very much!!! yes there is a bunch of cells that I need to enter.

Only those numbers with thousand hours are not changed example: 2400:30,

It's more likely that i will encounter hours (in thousand) entered as I
will
need Month to date reports.

All others (hours in hundreds, ex. 324:25) are fixed!!

I hope you can show me how to fixed those thousands :)

Again, thanks for you help and time.

"T. Valko" wrote:

There might be unseen characters like leading/trailing spaces:

<space31:00
31:00<space
<space31:00<space

If you have just that one cell try re-entering the value. If you a bunch
of
cells try:

Select the range of cells in question
Got the menu EditReplace
Find what: hit the space bar
Replace with: nothing, leave this empty
Replace all

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hello,
I am a workforce analyst, and needs to know how many hours of lates,
time
off, OT's, etc..we have...so I created a worksheet.

I exported the entered Hours from our database but unfortunately it is
in
General Format and seems to be locked. When multiplying it to 24 to
convert,
it gives #VALUE error data is of different type. I tried the TIMEVALUE
function to unlock but it does not show the correct number of hours if
it
goes beyond 24.

Example: Total OT hours is 31 hours (31:00 display from exported
database),
the format is General and it can't be changed...I used the TIMEVALUE
function
to "unlock" then multiplied it by 24, it gives 7 Hours only when
changed
to
number format, I realized it lacks 24 hours so I used the [h]:mm
(bracket)
format but still does not include all hours.

I don't have issue if the collected hours is lower than 24 hours.
Example, collected hours is 6:30 using TIMEVALUE and multiply by 24
gives
6.5 hours in number format this is good but when collected hours goes
beyond
24, ther goes the problem.

Ultimately what I want to see is...
Example: Collected Hours is 435:15 (display from exported database, in
General "locked" format)

Result should be 435.25 hours

Your help is greatly appreciated.

Thanks!

Time is locked to General format
 

I should start looking into macro eh?

Yeah, it can only improve your skills and ability.

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hi,

Thank you very for your help. This worked! I don't know macro yet, but
this
one is a great start! I should start looking into macro eh?

"T. Valko" wrote:

Hmmm...

You can enter times up to 9999:99:99.

There maybe some other unseen "junk" in there.

There is a macro at this site that will clean "junk" from your data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

I use this macro every time I import or copy from a website.

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hi there,

Thank you very much!!! yes there is a bunch of cells that I need to
enter.

Only those numbers with thousand hours are not changed example:
2400:30,

It's more likely that i will encounter hours (in thousand) entered as I
will
need Month to date reports.

All others (hours in hundreds, ex. 324:25) are fixed!!

I hope you can show me how to fixed those thousands :)

Again, thanks for you help and time.

"T. Valko" wrote:

There might be unseen characters like leading/trailing spaces:

<space31:00
31:00<space
<space31:00<space

If you have just that one cell try re-entering the value. If you a
bunch
of
cells try:

Select the range of cells in question
Got the menu EditReplace
Find what: hit the space bar
Replace with: nothing, leave this empty
Replace all

--
Biff
Microsoft Excel MVP


"al_gosc" <gosc_814 wrote in message
...
Hello,
I am a workforce analyst, and needs to know how many hours of lates,
time
off, OT's, etc..we have...so I created a worksheet.

I exported the entered Hours from our database but unfortunately it
is
in
General Format and seems to be locked. When multiplying it to 24 to
convert,
it gives #VALUE error data is of different type. I tried the
TIMEVALUE
function to unlock but it does not show the correct number of hours
if
it
goes beyond 24.

Example: Total OT hours is 31 hours (31:00 display from exported
database),
the format is General and it can't be changed...I used the TIMEVALUE
function
to "unlock" then multiplied it by 24, it gives 7 Hours only when
changed
to
number format, I realized it lacks 24 hours so I used the [h]:mm
(bracket)
format but still does not include all hours.

I don't have issue if the collected hours is lower than 24 hours.
Example, collected hours is 6:30 using TIMEVALUE and multiply by 24
gives
6.5 hours in number format this is good but when collected hours
goes
beyond
24, ther goes the problem.

Ultimately what I want to see is...
Example: Collected Hours is 435:15 (display from exported database,
in
General "locked" format)

Result should be 435.25 hours

Your help is greatly appreciated.

Thanks!