Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gina A.
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JE McGimpsey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format percentage as a decimal BSAT Student Excel Discussion (Misc queries) 2 October 20th 05 09:48 PM
Converting number or text to a Date Format samhain New Users to Excel 2 October 17th 05 02:28 PM
h:mm to decimal value Brent E Excel Discussion (Misc queries) 4 October 11th 05 07:09 PM
Converting a large vertical mailing list into a horizontal format Kevin VanHalen New Users to Excel 1 April 30th 05 12:23 PM
Keep number format after converting time to text DH Excel Discussion (Misc queries) 2 April 28th 05 07:18 PM


All times are GMT +1. The time now is 06:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"