Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting decimal calculation to h:mm format
I am working on a time sheet for work that is using the excel program. The
sheet has clock in and clock out times in several different cells. I am trying to get the cell at the end of the row to display the amount of time work in hours and minutes, not decimal. For instance it says 0.00 hours instead 0:00. I am using a template that currently uses a formula as follows to calculate the time: =IF((OR(D11='''',C11='''')),0,IF((D11<C11),((D11-C11)*24)+24,(D11-C11)*24)) Can someone please help me!!!! I am getting very flustered trying to figure this out. Thank You |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting decimal calculation to h:mm format
One way:
If your clock in and clock out are already times, don't convert to decimal - just subtract them. =IF(COUNT(C11:D11)<2,0,D11-C11+(D11<C11)) or =IF(COUNT(C11:D11)<2,0,MOD(D11-C11,1)) Format the cell as a time. In article , "Gina A." <Gina wrote: I am working on a time sheet for work that is using the excel program. The sheet has clock in and clock out times in several different cells. I am trying to get the cell at the end of the row to display the amount of time work in hours and minutes, not decimal. For instance it says 0.00 hours instead 0:00. I am using a template that currently uses a formula as follows to calculate the time: =IF((OR(D11='''',C11='''')),0,IF((D11<C11),((D11-C11)*24)+24,(D11-C11)*24)) Can someone please help me!!!! I am getting very flustered trying to figure this out. Thank You |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting decimal calculation to h:mm format
=IF(OR(D11='''',C11=''''),0,IF(D11<C11,D11-C11+1,D11-C11))
should work for you. just make sure to format the cell as time. Time is really stored as the number of days (ie 3:00 AM is stored as 0.125 and can be shown by formating as a number). That is why it was originally multiplied by 24. You will need to double check other cells that might link to this one. If there is a sum function you will need to format it as [h]:mm to get a similar format, or use a function like =24*Sum(E11:E15) and format as number to get a decimal. "Gina A." wrote: I am working on a time sheet for work that is using the excel program. The sheet has clock in and clock out times in several different cells. I am trying to get the cell at the end of the row to display the amount of time work in hours and minutes, not decimal. For instance it says 0.00 hours instead 0:00. I am using a template that currently uses a formula as follows to calculate the time: =IF((OR(D11='''',C11='''')),0,IF((D11<C11),((D11-C11)*24)+24,(D11-C11)*24)) Can someone please help me!!!! I am getting very flustered trying to figure this out. Thank You |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting decimal calculation to h:mm format
=IF(OR(C11="",D11=""),0,IF(D11<C11,D11-C11+1,D11-C11))
I just noticed your formula used single quotes. If you copy and paste my first one you will get an error. Use this on instead. "Sloth" wrote: =IF(OR(D11='''',C11=''''),0,IF(D11<C11,D11-C11+1,D11-C11)) should work for you. just make sure to format the cell as time. Time is really stored as the number of days (ie 3:00 AM is stored as 0.125 and can be shown by formating as a number). That is why it was originally multiplied by 24. You will need to double check other cells that might link to this one. If there is a sum function you will need to format it as [h]:mm to get a similar format, or use a function like =24*Sum(E11:E15) and format as number to get a decimal. "Gina A." wrote: I am working on a time sheet for work that is using the excel program. The sheet has clock in and clock out times in several different cells. I am trying to get the cell at the end of the row to display the amount of time work in hours and minutes, not decimal. For instance it says 0.00 hours instead 0:00. I am using a template that currently uses a formula as follows to calculate the time: =IF((OR(D11='''',C11='''')),0,IF((D11<C11),((D11-C11)*24)+24,(D11-C11)*24)) Can someone please help me!!!! I am getting very flustered trying to figure this out. Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format percentage as a decimal | Excel Discussion (Misc queries) | |||
Converting number or text to a Date Format | New Users to Excel | |||
h:mm to decimal value | Excel Discussion (Misc queries) | |||
Converting a large vertical mailing list into a horizontal format | New Users to Excel | |||
Keep number format after converting time to text | Excel Discussion (Misc queries) |