~Time Calculation

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have a formula that calculates time.
The following cells contain the following data.
Cell B381 contains 8:00 AM
Cell C381 contains 4:45 PM
Cell E381 contains =TEXT(C381-B381-TIME(,30,),"[h] \h\r\s: m \m\i\n\s")
which calculates to 8 hrs: 15 mins.

In cell G381, I want to display the time from cell E381 as 8.25
What formula do I use in G381?

Any help would be greatly appreciated
Thank you for your time in advance, Karen
 
One way:

=E281*24

XL stores times as fractional days, so multiplying by 24 gives integral
hours.
 
Put this formula in G381:

=(C381-B381-TIME(,30,))*24

and format the cell as General or Number with 2 dp.

Hope this helps.

Pete
 
Thank you for your help. I entered the formula =E381*24 and I received the
#VALUE! error
Any ideas?

JE McGimpsey said:
One way:

=E281*24

XL stores times as fractional days, so multiplying by 24 gives integral
hours.

Karen said:
I have a formula that calculates time.
The following cells contain the following data.
Cell B381 contains 8:00 AM
Cell C381 contains 4:45 PM
Cell E381 contains =TEXT(C381-B381-TIME(,30,),"[h] \h\r\s: m \m\i\n\s")
which calculates to 8 hrs: 15 mins.

In cell G381, I want to display the time from cell E381 as 8.25
What formula do I use in G381?

Any help would be greatly appreciated
Thank you for your time in advance, Karen
Click to expand...
Click to expand...
 
Thank you sooooooooooo much Pete! It works great!
That was so nice of you to help me and so quickly I might add.
Have a beautiful day, Karen

Pete_UK said:
Put this formula in G381:

=(C381-B381-TIME(,30,))*24

and format the cell as General or Number with 2 dp.

Hope this helps.

Pete

I have a formula that calculates time.
The following cells contain the following data.
Cell B381 contains 8:00 AM
Cell C381 contains 4:45 PM
Cell E381 contains =TEXT(C381-B381-TIME(,30,),"[h] \h\r\s: m \m\i\n\s")
which calculates to 8 hrs: 15 mins.

In cell G381, I want to display the time from cell E381 as 8.25
What formula do I use in G381?

Any help would be greatly appreciated
Thank you for your time in advance, Karen
Click to expand...
Click to expand...
 
Well that was dumb.

Most efficient is to change E381 to

=C381-B381-TIME(,30,)

and format the cell with the string in your text function
(Format/Cells/Number/Custom).

Or equivalently:

=MOD(C381-B381-1/48,1)



Karen said:
Thank you for your help. I entered the formula =E381*24 and I received the
#VALUE! error
Any ideas?

JE McGimpsey said:
One way:

=E281*24

XL stores times as fractional days, so multiplying by 24 gives integral
hours.

Karen said:
I have a formula that calculates time.
The following cells contain the following data.
Cell B381 contains 8:00 AM
Cell C381 contains 4:45 PM
Cell E381 contains =TEXT(C381-B381-TIME(,30,),"[h] \h\r\s: m \m\i\n\s")
which calculates to 8 hrs: 15 mins.

In cell G381, I want to display the time from cell E381 as 8.25
What formula do I use in G381?

Any help would be greatly appreciated
Thank you for your time in advance, Karen
Click to expand...
Click to expand...
Click to expand...
 
Back
Top