Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting time to numeric number
I need to know from someone about converting time into a numeric number Ex. I need to convert 8:00:00 to the #8 so i can divide the 8 hour into how many sales a person worked during that time to find out ho many sales were made per hour worked? HELP ME PLEASE AND VERY FAS PLEAS ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting time to numeric number
"brazpearl" wrote in message
... I need to know from someone about converting time into a numeric number. Ex. I need to convert 8:00:00 to the #8 so i can divide the 8 hours into how many sales a person worked during that time to find out how many sales were made per hour worked? Assuming that your time value is the result of a calculation (i.e. end time - start time) and not a directly entered value, you can convert time into hours by multiplying by 24. So if you have: A 1 12:00 PM 2 8:00 PM 3 =A2-A1 4 =A3*24 Cell A4 will have the number of hours worked as a regular number, rather than Excel's DateSerial notation. Be sure to change the number format of cell A4 to General after you enter the formula or you'll get a strange looking result because it will be given a DateTime format by default. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting time to numeric number
Assume 8:00:00 in cell A1, then in Cell B1 = A1 * 24. HTH Raymon ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting time to numeric number
I THANK YOU FOR TRYING BUT IT IS A BIT MORE DIFFICULT THAN THAT LET M EXPLAIN A BIT BETTER. WORKSHEET CALLED HOURS HAS ALL THE HOURS IN THE FORMAT 8:00:00 FOR EXAMPLE THEN IN WORKSHEET CALL SALES IN AN OVERVIEW WORKSHEET I NEED TO COME UP WITH A LONG FORMULA TO TAK OF IT ALL FOR THE COLUMN SALES PER HOUR TO CALCULATE SO IT NEEDS TO TAKE THE 8:00:00 IN A FORMULA CONVERT TO A NUMBER THE TAKE THE SALES IN THE SALES WORKSHEET DIVIDE THEN AND POPULATE TH SALES PER HOUR. QUESTION HOW DO I COME UP WITH SUCH A FORMULA WITH OU CREATING ANOTHER COLUMN. TO DO WHAT I NEED IT TO. YOUR ANSWERS WERE SIMLPLE IF I WAS DOING IT THAT WAY. i NEED ON REALLY BIG FORMULA TO DO IT ALL. I HOPE THIS IS CLEARLY TO WHAT I NEED HELP WITH.:confused ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting time to numeric number
As you said, your formula is rather complicated. Would you mind disclos your formula you write. Raymon ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting time to numeric number
The formula is not very complicated based on your description. Assuming you have hours as a DateTime in cell A3 on the Hours worksheet and you have Sales as some number of units in cell A3 on the Sales worksheet, then the formula to calculate sales per hour on your Overview worksheet would simply be: =Sales!A3/(Hours!A3*24) -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "brazpearl" wrote in message ... I THANK YOU FOR TRYING BUT IT IS A BIT MORE DIFFICULT THAN THAT LET ME EXPLAIN A BIT BETTER. WORKSHEET CALLED HOURS HAS ALL THE HOURS IN THE FORMAT 8:00:00 FOR EXAMPLE THEN IN WORKSHEET CALL SALES IN AN OVERVIEW WORKSHEET I NEED TO COME UP WITH A LONG FORMULA TO TAKE OF IT ALL FOR THE COLUMN SALES PER HOUR TO CALCULATE SO IT NEEDS TO TAKE THE 8:00:00 IN A FORMULA CONVERT TO A NUMBER THEN TAKE THE SALES IN THE SALES WORKSHEET DIVIDE THEN AND POPULATE THE SALES PER HOUR. QUESTION HOW DO I COME UP WITH SUCH A FORMULA WITH OUT CREATING ANOTHER COLUMN. TO DO WHAT I NEED IT TO. YOUR ANSWERS WERE SIMLPLE IF I WAS DOING IT THAT WAY. i NEED ONE REALLY BIG FORMULA TO DO IT ALL. I HOPE THIS IS CLEARLY TO WHAT I NEED HELP WITH. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting time to numeric number
=24/(8:00:00*24) Slaes divided by times to get sales per hour yes but an error messag comes up if you are talking like this then there is an error. the hours need to be in this format 8:00:00 and then converted to thi format 8 a whole numeric numer or a decimal number for your formula t work. Again thanks for the effort. I will keep trying any suggestions ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting time to numeric number
If you want to enter the actual time in the formula itself you'll have to do it like this: =24/(TIMEVALUE("8:00:00")*24) -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "brazpearl" wrote in message ... =24/(8:00:00*24) Slaes divided by times to get sales per hour yes but an error message comes up if you are talking like this then there is an error. the hours need to be in this format 8:00:00 and then converted to this format 8 a whole numeric numer or a decimal number for your formula to work. Again thanks for the effort. I will keep trying any suggestions. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Time in a number | Excel Discussion (Misc queries) | |||
Converting time to number, i.e. 2:32 to 2.32 | Excel Worksheet Functions | |||
Converting time from text to numeric | Excel Worksheet Functions | |||
converting number to time | Excel Discussion (Misc queries) | |||
Converting whole number to TIME | Excel Discussion (Misc queries) |