Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change time into decimel unit
Hi !
How can I change the time, which if changed to a decimal number, but gives a figure of 1,02 for an hour, but is there any function that can produce result as just (1) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change time into decimel unit
Hi Shariq
I'm not entirely sure I understand what you want, but if you have a time value in a cell, you could format it (custom format that is) as: hh which would only display the hours of the timevalue, so if the full time was say 15:32:00 it would display 15 Alternatively, if you want the time value as an integer (ie for the number of hours) perhaps you could use: =INT(A1*24) where A1 holds your time. Hope this helps! Richard Shariq wrote: Hi ! How can I change the time, which if changed to a decimal number, but gives a figure of 1,02 for an hour, but is there any function that can produce result as just (1) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change time into decimel unit
Hi Richard!
I think I didn't put my querry right, actually I am using this formula to be able to calculate time even if it is spent is less then 1 hrs, =MAX(1,(E7-B7+(E7<B7))*24) but now the outcome I receive is not in digit 1,00 but 1,04 and this is what creates problem, because I need to multiply those hours spent into Amount, and then it mutiplies not with 1 but with 1,04, so have you any clue how to help this problem. "RichardSchollar" wrote: Hi Shariq I'm not entirely sure I understand what you want, but if you have a time value in a cell, you could format it (custom format that is) as: hh which would only display the hours of the timevalue, so if the full time was say 15:32:00 it would display 15 Alternatively, if you want the time value as an integer (ie for the number of hours) perhaps you could use: =INT(A1*24) where A1 holds your time. Hope this helps! Richard Shariq wrote: Hi ! How can I change the time, which if changed to a decimal number, but gives a figure of 1,02 for an hour, but is there any function that can produce result as just (1) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change time into decimel unit
Shariq
You can wrap the whole thing with the INT function: =INT(MAX(1,(E7-B7+(E7<B7))*24)) which should sort out your issue :-) Hope it helps! Richard Shariq wrote: Hi Richard! I think I didn't put my querry right, actually I am using this formula to be able to calculate time even if it is spent is less then 1 hrs, =MAX(1,(E7-B7+(E7<B7))*24) but now the outcome I receive is not in digit 1,00 but 1,04 and this is what creates problem, because I need to multiply those hours spent into Amount, and then it mutiplies not with 1 but with 1,04, so have you any clue how to help this problem. "RichardSchollar" wrote: Hi Shariq I'm not entirely sure I understand what you want, but if you have a time value in a cell, you could format it (custom format that is) as: hh which would only display the hours of the timevalue, so if the full time was say 15:32:00 it would display 15 Alternatively, if you want the time value as an integer (ie for the number of hours) perhaps you could use: =INT(A1*24) where A1 holds your time. Hope this helps! Richard Shariq wrote: Hi ! How can I change the time, which if changed to a decimal number, but gives a figure of 1,02 for an hour, but is there any function that can produce result as just (1) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change time into decimel unit
HI RICHARD!
THANK YOU ONCE AGAIN, IT IS WORKING, BUT WITH THIS FORMULA IT DOSEN'T COUNT ABOVE 1, NO MATTER WHAT THE TIME DIFFERENCE IS LESS OR MORE THEN AN HOUR IT KEEPS ON SHOWING ONLY 1, SHOULD I PUT AN (IF) ARGUMENT IN IT MAY BE. "RichardSchollar" wrote: Shariq You can wrap the whole thing with the INT function: =INT(MAX(1,(E7-B7+(E7<B7))*24)) which should sort out your issue :-) Hope it helps! Richard Shariq wrote: Hi Richard! I think I didn't put my querry right, actually I am using this formula to be able to calculate time even if it is spent is less then 1 hrs, =MAX(1,(E7-B7+(E7<B7))*24) but now the outcome I receive is not in digit 1,00 but 1,04 and this is what creates problem, because I need to multiply those hours spent into Amount, and then it mutiplies not with 1 but with 1,04, so have you any clue how to help this problem. "RichardSchollar" wrote: Hi Shariq I'm not entirely sure I understand what you want, but if you have a time value in a cell, you could format it (custom format that is) as: hh which would only display the hours of the timevalue, so if the full time was say 15:32:00 it would display 15 Alternatively, if you want the time value as an integer (ie for the number of hours) perhaps you could use: =INT(A1*24) where A1 holds your time. Hope this helps! Richard Shariq wrote: Hi ! How can I change the time, which if changed to a decimal number, but gives a figure of 1,02 for an hour, but is there any function that can produce result as just (1) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change time into decimel unit
It should go above 1. What values do you have in E7 and in B7? What do you
get if you put the formula =E7-B7 into a cell and format it as time? What do you get if you put =(E7-B7)*24 into a cell and format it as general or number? Note also that the INT will round down, so 1:45 (1.75 hours) will round down to 1 (and, as Richard said, 15:32 will round to 15). If you want to round to the nearest hour, use =ROUND(MAX(1,(E7-B7+(E7<B7))*24),0) -- David Biddulph "Shariq" wrote in message ... HI RICHARD! THANK YOU ONCE AGAIN, IT IS WORKING, BUT WITH THIS FORMULA IT DOSEN'T COUNT ABOVE 1, NO MATTER WHAT THE TIME DIFFERENCE IS LESS OR MORE THEN AN HOUR IT KEEPS ON SHOWING ONLY 1, SHOULD I PUT AN (IF) ARGUMENT IN IT MAY BE. "RichardSchollar" wrote: Shariq You can wrap the whole thing with the INT function: =INT(MAX(1,(E7-B7+(E7<B7))*24)) which should sort out your issue :-) Hope it helps! Richard Shariq wrote: Hi Richard! I think I didn't put my querry right, actually I am using this formula to be able to calculate time even if it is spent is less then 1 hrs, =MAX(1,(E7-B7+(E7<B7))*24) but now the outcome I receive is not in digit 1,00 but 1,04 and this is what creates problem, because I need to multiply those hours spent into Amount, and then it mutiplies not with 1 but with 1,04, so have you any clue how to help this problem. "RichardSchollar" wrote: Hi Shariq I'm not entirely sure I understand what you want, but if you have a time value in a cell, you could format it (custom format that is) as: hh which would only display the hours of the timevalue, so if the full time was say 15:32:00 it would display 15 Alternatively, if you want the time value as an integer (ie for the number of hours) perhaps you could use: =INT(A1*24) where A1 holds your time. Hope this helps! Richard Shariq wrote: Hi ! How can I change the time, which if changed to a decimal number, but gives a figure of 1,02 for an hour, but is there any function that can produce result as just (1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i change all text case in a given worksheet at one time | Excel Worksheet Functions | |||
How can I change time format to decimal without losing hours? | Excel Discussion (Misc queries) | |||
change date based on time | Excel Discussion (Misc queries) | |||
Time Stamp without change | Excel Discussion (Misc queries) | |||
Time Stamp-With Change | Excel Discussion (Misc queries) |