Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How calculate difference between [t]:mm-format and general format | Excel Worksheet Functions | |||
Format: General - Text - General | Excel Worksheet Functions | |||
excel numbers in general format i cant add cant change format | Excel Worksheet Functions | |||
how do I convert a general number to a time format? | Excel Worksheet Functions | |||
Change General Format to Currency Format | Excel Worksheet Functions |