Time is locked to General format

[al_gosc]

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!

 

[T. Valko]

There might be unseen characters like leading/trailing spaces:

<space>31:00
31:00<space>
<space>31: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 Edit>Replace
Find what: hit the space bar
Replace with: nothing, leave this empty
Replace all

 

[al_gosc]

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.

There might be unseen characters like leading/trailing spaces:

<space>31:00
31:00<space>
<space>31: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 Edit>Replace
Find what: hit the space bar
Replace with: nothing, leave this empty
Replace all

--
Biff
Microsoft Excel MVP

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!

 

[T. Valko]

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

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.

There might be unseen characters like leading/trailing spaces:

<space>31:00
31:00<space>
<space>31: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 Edit>Replace
Find what: hit the space bar
Replace with: nothing, leave this empty
Replace all

--
Biff
Microsoft Excel MVP

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!

 

[al_gosc]

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?

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

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.

There might be unseen characters like leading/trailing spaces:

<space>31:00
31:00<space>
<space>31: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 Edit>Replace
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!

 

[T. Valko]

I should start looking into macro eh?

Yeah, it can only improve your skills and ability.

--
Biff
Microsoft Excel MVP

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?

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

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.

:

There might be unseen characters like leading/trailing spaces:

<space>31:00
31:00<space>
<space>31: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 Edit>Replace
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!