Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
Hi
I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account from http://www.teranews.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
Assume the start time is in A1 and the finish time in B1, both in
Excel time format, put this formula in C1: =(B1-A1)*24 Format C1 as a number with 2 dp. Hope this helps. Pete On Sep 18, 10:43 pm, "Ian" wrote: Hi I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account fromhttp://www.teranews.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
Thanks Pete I'll try that Cheers Ian "Pete_UK" wrote in message oups.com... Assume the start time is in A1 and the finish time in B1, both in Excel time format, put this formula in C1: =(B1-A1)*24 Format C1 as a number with 2 dp. Hope this helps. Pete On Sep 18, 10:43 pm, "Ian" wrote: Hi I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account fromhttp://www.teranews.com -- Posted via a free Usenet account from http://www.teranews.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
You're welcome, Ian.
Pete On Sep 19, 12:06 am, "Ian" wrote: Thanks Pete I'll try that Cheers Ian "Pete_UK" wrote in message oups.com... Assume the start time is in A1 and the finish time in B1, both in Excel time format, put this formula in C1: =(B1-A1)*24 Format C1 as a number with 2 dp. Hope this helps. Pete On Sep 18, 10:43 pm, "Ian" wrote: Hi I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account fromhttp://www.teranews.com -- Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
If you don't want to have to worry about changeing the formatting manually,
you could also try: C1=VALUE(TEXT(((B1-A1)*24),"0.00")) Where B1="11:30" and A1="7:15" Thanks, Mike "Ian" wrote: Hi I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account from http://www.teranews.com |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
Sorry, but I'm confused, Mike. There's obviously some subtlety that has
escaped me. Why have you used TEXT to convert a number to text, and then VALUE to change it back to a number again? What was wrong with simply =(B1-A1)*24 ? -- David Biddulph "Michael Bowers" wrote in message ... If you don't want to have to worry about changeing the formatting manually, you could also try: C1=VALUE(TEXT(((B1-A1)*24),"0.00")) Where B1="11:30" and A1="7:15" Thanks, Mike "Ian" wrote: Hi I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account from http://www.teranews.com |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
David, I think this was to force the formatting to "0.00" without
having to do it manually. Pete On Sep 19, 8:45 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: Sorry, but I'm confused, Mike. There's obviously some subtlety that has escaped me. Why have you used TEXT to convert a number to text, and then VALUE to change it back to a number again? What was wrong with simply =(B1-A1)*24 ? -- David Biddulph "Michael Bowers" wrote in message ... If you don't want to have to worry about changeing the formatting manually, you could also try: C1=VALUE(TEXT(((B1-A1)*24),"0.00")) Where B1="11:30" and A1="7:15" Thanks, Mike "Ian" wrote: Hi I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
I wondered that, but after the VALUE aren't you back to a number that needs
formatting if you want anything other than the default? Doesn't the default show as 4, rather than 4.00, if you have a 4 hour difference, despite the TEXT function? -- David Biddulph "Pete_UK" wrote in message oups.com... David, I think this was to force the formatting to "0.00" without having to do it manually. Pete On Sep 19, 8:45 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: Sorry, but I'm confused, Mike. There's obviously some subtlety that has escaped me. Why have you used TEXT to convert a number to text, and then VALUE to change it back to a number again? What was wrong with simply =(B1-A1)*24 ? -- David Biddulph "Michael Bowers" wrote in message ... If you don't want to have to worry about changeing the formatting manually, you could also try: C1=VALUE(TEXT(((B1-A1)*24),"0.00")) Where B1="11:30" and A1="7:15" Thanks, Mike "Ian" wrote: Hi I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating hours
Yeah, you're right David - I've just tried it. Maybe Michael will come
back and explain his reasoning. Pete On Sep 19, 11:04 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: I wondered that, but after the VALUE aren't you back to a number that needs formatting if you want anything other than the default? Doesn't the default show as 4, rather than 4.00, if you have a 4 hour difference, despite the TEXT function? -- David Biddulph "Pete_UK" wrote in message oups.com... David, I think this was to force the formatting to "0.00" without having to do it manually. Pete On Sep 19, 8:45 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: Sorry, but I'm confused, Mike. There's obviously some subtlety that has escaped me. Why have you used TEXT to convert a number to text, and then VALUE to change it back to a number again? What was wrong with simply =(B1-A1)*24 ? -- David Biddulph "Michael Bowers" wrote in message ... If you don't want to have to worry about changeing the formatting manually, you could also try: C1=VALUE(TEXT(((B1-A1)*24),"0.00")) Where B1="11:30" and A1="7:15" Thanks, Mike "Ian" wrote: Hi I need to do a sheet where I put in a time, 7:15 for example in a cell. In another cell I would enter 11:30 for example. The difference in time is 4.25 hours. What formula would I use to get this answer? Thanks for any help -- Posted via a free Usenet account fromhttp://www.teranews.com-Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating how many hours between one date and another | Excel Discussion (Misc queries) | |||
Calculating working hours | Excel Discussion (Misc queries) | |||
calculating hours | Excel Worksheet Functions | |||
Calculating working hours | Excel Discussion (Misc queries) | |||
Calculating hours for a timesheet | Excel Worksheet Functions |