Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 and/or 2002
I want to subtract 2 hours from 7:00:00 AM (cell A1), and have the result
(5:00:00 AM) show up in Cell C1. So in cell A1 I wrote "7:00:00 AM" (in the Custom Number format of h:mm:ss AM/PM); then in cell C1 I wrote "=A1-B1" (in the same Custom format as before). Now, I want cell B1 to equal 2:00 (2 hours). But when I write "2:00" in B1, Excel changes it to "2:00:00 AM". However, I want the cell to represent hours--not AM or PM, but just plain old hours. (For example: Someone will tell you to "see me in 2 hours." But they wouldn't tell you to "see me in 2 hours AM"?!! Similarly, one says to "subtract 2 hours from 7:00 AM". But it is incorrect to say "subtract 2 hours AM from 7:00 AM") Now, I understand that even with Excel changing it to 2 hours AM (2:00:00 AM), I will still get the desired result of 5:00 AM. However, WHAT I WANT TO KNOW IS IF THERE IS ANY WAY TO HAVE EXCEL RECOGNIZE A TIME TO SIMPLY BE TIME. NOT AM OR PM BUT JUST A TIME PERIOD OF 2 HOURS? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 and/or 2002
From my experience, Excel does this only in the formula bar. It will abide by
your data entry in the actual cell, unless you've formatted it to something different. So, either pre-format B1 as General or post-format it as h:mm, and ignore (or turn off) the formula bar. -- Regards, Fred "Display Name" wrote in message ... I want to subtract 2 hours from 7:00:00 AM (cell A1), and have the result (5:00:00 AM) show up in Cell C1. So in cell A1 I wrote "7:00:00 AM" (in the Custom Number format of h:mm:ss AM/PM); then in cell C1 I wrote "=A1-B1" (in the same Custom format as before). Now, I want cell B1 to equal 2:00 (2 hours). But when I write "2:00" in B1, Excel changes it to "2:00:00 AM". However, I want the cell to represent hours--not AM or PM, but just plain old hours. (For example: Someone will tell you to "see me in 2 hours." But they wouldn't tell you to "see me in 2 hours AM"?!! Similarly, one says to "subtract 2 hours from 7:00 AM". But it is incorrect to say "subtract 2 hours AM from 7:00 AM") Now, I understand that even with Excel changing it to 2 hours AM (2:00:00 AM), I will still get the desired result of 5:00 AM. However, WHAT I WANT TO KNOW IS IF THERE IS ANY WAY TO HAVE EXCEL RECOGNIZE A TIME TO SIMPLY BE TIME. NOT AM OR PM BUT JUST A TIME PERIOD OF 2 HOURS? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 and/or 2002
Display Name
If you formate the cell with 2:00 in it as h:mm it will display as i think you want. Mike Rogers "Display Name" wrote: I want to subtract 2 hours from 7:00:00 AM (cell A1), and have the result (5:00:00 AM) show up in Cell C1. So in cell A1 I wrote "7:00:00 AM" (in the Custom Number format of h:mm:ss AM/PM); then in cell C1 I wrote "=A1-B1" (in the same Custom format as before). Now, I want cell B1 to equal 2:00 (2 hours). But when I write "2:00" in B1, Excel changes it to "2:00:00 AM". However, I want the cell to represent hours--not AM or PM, but just plain old hours. (For example: Someone will tell you to "see me in 2 hours." But they wouldn't tell you to "see me in 2 hours AM"?!! Similarly, one says to "subtract 2 hours from 7:00 AM". But it is incorrect to say "subtract 2 hours AM from 7:00 AM") Now, I understand that even with Excel changing it to 2 hours AM (2:00:00 AM), I will still get the desired result of 5:00 AM. However, WHAT I WANT TO KNOW IS IF THERE IS ANY WAY TO HAVE EXCEL RECOGNIZE A TIME TO SIMPLY BE TIME. NOT AM OR PM BUT JUST A TIME PERIOD OF 2 HOURS? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 and/or 2002
Not only will formating the cell as h:mm not change anything, but right when
2:00 is typed into cell B2, Excel automatically formats it as h:mm. Yet in the formula bar it will still say 2:00:00 AM. Nevertheless, thank you for trying to help me out. "Mike Rogers" wrote: Display Name If you formate the cell with 2:00 in it as h:mm it will display as i think you want. Mike Rogers "Display Name" wrote: I want to subtract 2 hours from 7:00:00 AM (cell A1), and have the result (5:00:00 AM) show up in Cell C1. So in cell A1 I wrote "7:00:00 AM" (in the Custom Number format of h:mm:ss AM/PM); then in cell C1 I wrote "=A1-B1" (in the same Custom format as before). Now, I want cell B1 to equal 2:00 (2 hours). But when I write "2:00" in B1, Excel changes it to "2:00:00 AM". However, I want the cell to represent hours--not AM or PM, but just plain old hours. (For example: Someone will tell you to "see me in 2 hours." But they wouldn't tell you to "see me in 2 hours AM"?!! Similarly, one says to "subtract 2 hours from 7:00 AM". But it is incorrect to say "subtract 2 hours AM from 7:00 AM") Now, I understand that even with Excel changing it to 2 hours AM (2:00:00 AM), I will still get the desired result of 5:00 AM. However, WHAT I WANT TO KNOW IS IF THERE IS ANY WAY TO HAVE EXCEL RECOGNIZE A TIME TO SIMPLY BE TIME. NOT AM OR PM BUT JUST A TIME PERIOD OF 2 HOURS? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 and/or 2002
I do realize that it is only in the formula bar that 2:00:00 AM is shown, and
when "just viewing the cell" it will display 2:00. However, my question is if it is possible to have Excel not just display the 2 hours as 2 hours, but how to have Excel actually recognize a time to simply be time. (and it is in the formula bar from where we know what Excel is actually doing) I apologize if I did not explain my question like this to begin with. "Fred Smith" wrote: From my experience, Excel does this only in the formula bar. It will abide by your data entry in the actual cell, unless you've formatted it to something different. So, either pre-format B1 as General or post-format it as h:mm, and ignore (or turn off) the formula bar. -- Regards, Fred "Display Name" wrote in message ... I want to subtract 2 hours from 7:00:00 AM (cell A1), and have the result (5:00:00 AM) show up in Cell C1. So in cell A1 I wrote "7:00:00 AM" (in the Custom Number format of h:mm:ss AM/PM); then in cell C1 I wrote "=A1-B1" (in the same Custom format as before). Now, I want cell B1 to equal 2:00 (2 hours). But when I write "2:00" in B1, Excel changes it to "2:00:00 AM". However, I want the cell to represent hours--not AM or PM, but just plain old hours. (For example: Someone will tell you to "see me in 2 hours." But they wouldn't tell you to "see me in 2 hours AM"?!! Similarly, one says to "subtract 2 hours from 7:00 AM". But it is incorrect to say "subtract 2 hours AM from 7:00 AM") Now, I understand that even with Excel changing it to 2 hours AM (2:00:00 AM), I will still get the desired result of 5:00 AM. However, WHAT I WANT TO KNOW IS IF THERE IS ANY WAY TO HAVE EXCEL RECOGNIZE A TIME TO SIMPLY BE TIME. NOT AM OR PM BUT JUST A TIME PERIOD OF 2 HOURS? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 and/or 2002
The formula bar is based on your regional settings. Go to Regional
and Language Settings in Control Panel, and click Customize, then go over to the Time tab. Use this time format: H:mm And take out AM or PM designations. On Dec 4, 12:19 am, Display Name wrote: I do realize that it is only in the formula bar that 2:00:00 AM is shown, and when "just viewing the cell" it will display 2:00. However, my question is if it is possible to have Excel not just display the 2 hours as 2 hours, but how to have Excel actually recognize a time to simply be time. (and it is in the formula bar from where we know what Excel is actually doing) I apologize if I did not explain my question like this to begin with. "Fred Smith" wrote: From my experience, Excel does this only in the formula bar. It will abide by your data entry in the actual cell, unless you've formatted it to something different. So, either pre-format B1 as General or post-format it as h:mm, and ignore (or turn off) the formula bar. -- Regards, Fred "Display Name" wrote in message ... I want to subtract 2 hours from 7:00:00 AM (cell A1), and have the result (5:00:00 AM) show up in Cell C1. So in cell A1 I wrote "7:00:00 AM" (in the Custom Number format of h:mm:ss AM/PM); then in cell C1 I wrote "=A1-B1" (in the same Custom format as before). Now, I want cell B1 to equal 2:00 (2 hours). But when I write "2:00" in B1, Excel changes it to "2:00:00 AM". However, I want the cell to represent hours--not AM or PM, but just plain old hours. (For example: Someone will tell you to "see me in 2 hours." But they wouldn't tell you to "see me in 2 hours AM"?!! Similarly, one says to "subtract 2 hours from 7:00 AM". But it is incorrect to say "subtract 2 hours AM from 7:00 AM") Now, I understand that even with Excel changing it to 2 hours AM (2:00:00 AM), I will still get the desired result of 5:00 AM. However, WHAT I WANT TO KNOW IS IF THERE IS ANY WAY TO HAVE EXCEL RECOGNIZE A TIME TO SIMPLY BE TIME. NOT AM OR PM BUT JUST A TIME PERIOD OF 2 HOURS?- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 and/or 2002
But it is recognizing 2:00 as just 2 hours. When you enter 2:00 in a cell, and
use it in a formula, it will be treated as 2 hours, regardless of how it's formatted. What problems have you have with Excel treating 2:00 as something other than 2 hours? -- Regards, Fred "Display Name" wrote in message ... I do realize that it is only in the formula bar that 2:00:00 AM is shown, and when "just viewing the cell" it will display 2:00. However, my question is if it is possible to have Excel not just display the 2 hours as 2 hours, but how to have Excel actually recognize a time to simply be time. (and it is in the formula bar from where we know what Excel is actually doing) I apologize if I did not explain my question like this to begin with. "Fred Smith" wrote: From my experience, Excel does this only in the formula bar. It will abide by your data entry in the actual cell, unless you've formatted it to something different. So, either pre-format B1 as General or post-format it as h:mm, and ignore (or turn off) the formula bar. -- Regards, Fred "Display Name" wrote in message ... I want to subtract 2 hours from 7:00:00 AM (cell A1), and have the result (5:00:00 AM) show up in Cell C1. So in cell A1 I wrote "7:00:00 AM" (in the Custom Number format of h:mm:ss AM/PM); then in cell C1 I wrote "=A1-B1" (in the same Custom format as before). Now, I want cell B1 to equal 2:00 (2 hours). But when I write "2:00" in B1, Excel changes it to "2:00:00 AM". However, I want the cell to represent hours--not AM or PM, but just plain old hours. (For example: Someone will tell you to "see me in 2 hours." But they wouldn't tell you to "see me in 2 hours AM"?!! Similarly, one says to "subtract 2 hours from 7:00 AM". But it is incorrect to say "subtract 2 hours AM from 7:00 AM") Now, I understand that even with Excel changing it to 2 hours AM (2:00:00 AM), I will still get the desired result of 5:00 AM. However, WHAT I WANT TO KNOW IS IF THERE IS ANY WAY TO HAVE EXCEL RECOGNIZE A TIME TO SIMPLY BE TIME. NOT AM OR PM BUT JUST A TIME PERIOD OF 2 HOURS? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 and/or 2002
You can multiply any date/time value to get the number of hours. For
example, this formula: =((DATEVALUE(cell1)+TIMEVALUE(cell2))- (DATEVALUE(cell3)+TIMEVALUE(cell4)))* 24 Will give you the number of hours between day cell1 at time cell2 and day cell3 at time cell4. On Dec 4, 4:25 pm, "Fred Smith" wrote: But it is recognizing 2:00 as just 2 hours. When you enter 2:00 in a cell, and use it in a formula, it will be treated as 2 hours, regardless of how it's formatted. What problems have you have with Excel treating 2:00 as something other than 2 hours? -- Regards, Fred "Display Name" wrote in message ... I do realize that it is only in the formula bar that 2:00:00 AM is shown, and when "just viewing the cell" it will display 2:00. However, my question is if it is possible to have Excel not just display the 2 hours as 2 hours, but how to have Excel actually recognize a time to simply be time. (and it is in the formula bar from where we know what Excel is actually doing) I apologize if I did not explain my question like this to begin with. "Fred Smith" wrote: From my experience, Excel does this only in the formula bar. It will abide by your data entry in the actual cell, unless you've formatted it to something different. So, either pre-format B1 as General or post-format it as h:mm, and ignore (or turn off) the formula bar. -- Regards, Fred "Display Name" wrote in message ... I want to subtract 2 hours from 7:00:00 AM (cell A1), and have the result (5:00:00 AM) show up in Cell C1. So in cell A1 I wrote "7:00:00 AM" (in the Custom Number format of h:mm:ss AM/PM); then in cell C1 I wrote "=A1-B1" (in the same Custom format as before). Now, I want cell B1 to equal 2:00 (2 hours). But when I write "2:00" in B1, Excel changes it to "2:00:00 AM". However, I want the cell to represent hours--not AM or PM, but just plain old hours. (For example: Someone will tell you to "see me in 2 hours." But they wouldn't tell you to "see me in 2 hours AM"?!! Similarly, one says to "subtract 2 hours from 7:00 AM". But it is incorrect to say "subtract 2 hours AM from 7:00 AM") Now, I understand that even with Excel changing it to 2 hours AM (2:00:00 AM), I will still get the desired result of 5:00 AM. However, WHAT I WANT TO KNOW IS IF THERE IS ANY WAY TO HAVE EXCEL RECOGNIZE A TIME TO SIMPLY BE TIME. NOT AM OR PM BUT JUST A TIME PERIOD OF 2 HOURS? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 and 2007 on same computer | Excel Discussion (Misc queries) | |||
Excel 2002: How is the Excel 2007 file format open in Excel 2002 ? | Excel Discussion (Misc queries) | |||
Running Excel 2007 & 2002 on the same machine | Setting up and Configuration of Excel | |||
Exel 2002/2007-Always get 31 Oct 06 | Excel Discussion (Misc queries) | |||
Outlook 2002 calendar dates exported to Excel 2002 sort incorrectl | Excel Worksheet Functions |