Converting decimal calculation to h:mm format

[Gina A.]

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

 

[JE McGimpsey]

One way:

If your clock in and clock out are already times, don't convert to
decimal - just subtract them.

=IF(COUNT(C1111)<>2,0,D11-C11+(D11<C11))

or

=IF(COUNT(C1111)<>2,0,MOD(D11-C11,1))

Format the cell as a time.

 

[Sloth]

=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.

 

[Sloth]

=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.

=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.

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