| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date time format question please
Can anyone please help me with the following formating question?
A B C D 3/27/2007 11 49 3/27/2007 11:49 I inherited a spreadsheet that has the hour and minute columns B&C formated as general. I tried to change the formating so I can get Column D to add the columns. I get an incorrect date .... instead it displays 5/26/2001 0:00 A2+B2+C2 should read 3/27/2007 11:49 Thank you in advance for your time and help Deb |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date time format question please
To Excel:
€¢ Days are whole numbers €¢ Times are fractions of days (eg: 12 Noon = 0.5...half a day) Consequently... Each HOUR is 1/24 of a day Each MINUTE is 1/60 of an HOUR So....to do what you want, try this: =A1+B1/24+C1/(24*60) Remember to format as a date/time Does that help? *********** Regards, Ron XL2003, WinXP "ferde" wrote: Can anyone please help me with the following formating question? A B C D 3/27/2007 11 49 3/27/2007 11:49 I inherited a spreadsheet that has the hour and minute columns B&C formated as general. I tried to change the formating so I can get Column D to add the columns. I get an incorrect date .... instead it displays 5/26/2001 0:00 A2+B2+C2 should read 3/27/2007 11:49 Thank you in advance for your time and help Deb |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date time format question please
Yes... it works perfect. Thank you so much I've been researching this myself
all afternoon. "Ron Coderre" wrote: To Excel: €¢ Days are whole numbers €¢ Times are fractions of days (eg: 12 Noon = 0.5...half a day) Consequently... Each HOUR is 1/24 of a day Each MINUTE is 1/60 of an HOUR So....to do what you want, try this: =A1+B1/24+C1/(24*60) Remember to format as a date/time Does that help? *********** Regards, Ron XL2003, WinXP "ferde" wrote: Can anyone please help me with the following formating question? A B C D 3/27/2007 11 49 3/27/2007 11:49 I inherited a spreadsheet that has the hour and minute columns B&C formated as general. I tried to change the formating so I can get Column D to add the columns. I get an incorrect date .... instead it displays 5/26/2001 0:00 A2+B2+C2 should read 3/27/2007 11:49 Thank you in advance for your time and help Deb |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date time format question please
3/27/2007 11 49 3/27/07 11:49
11/28/2007 13 55 3/27/07 11:49 3/29/2007 20 10 3/27/07 11:49 5/30/2007 7 15 3/27/07 11:49 I noticed when I dragged the formula down the column it didnt actually compute correctly until I was ready to give up and so I just hit SAVE until tomorrow ....then boom it displayed correctly all the way down the column. I dont know if this is a fluke or not ? "ferde" wrote: Yes... it works perfect. Thank you so much I've been researching this myself all afternoon. "Ron Coderre" wrote: To Excel: €¢ Days are whole numbers €¢ Times are fractions of days (eg: 12 Noon = 0.5...half a day) Consequently... Each HOUR is 1/24 of a day Each MINUTE is 1/60 of an HOUR So....to do what you want, try this: =A1+B1/24+C1/(24*60) Remember to format as a date/time Does that help? *********** Regards, Ron XL2003, WinXP "ferde" wrote: Can anyone please help me with the following formating question? A B C D 3/27/2007 11 49 3/27/2007 11:49 I inherited a spreadsheet that has the hour and minute columns B&C formated as general. I tried to change the formating so I can get Column D to add the columns. I get an incorrect date .... instead it displays 5/26/2001 0:00 A2+B2+C2 should read 3/27/2007 11:49 Thank you in advance for your time and help Deb |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date time format question please
It sounds like you've got Calculation set to Manual, with "Recalculate before
Save" checked. From the Excel main menu: <tools<options<calculation tab Are those settings checked instead of "Automatic"? Does that help? *********** Regards, Ron XL2003, WinXP "ferde" wrote: 3/27/2007 11 49 3/27/07 11:49 11/28/2007 13 55 3/27/07 11:49 3/29/2007 20 10 3/27/07 11:49 5/30/2007 7 15 3/27/07 11:49 I noticed when I dragged the formula down the column it didnt actually compute correctly until I was ready to give up and so I just hit SAVE until tomorrow ....then boom it displayed correctly all the way down the column. I dont know if this is a fluke or not ? "ferde" wrote: Yes... it works perfect. Thank you so much I've been researching this myself all afternoon. "Ron Coderre" wrote: To Excel: €¢ Days are whole numbers €¢ Times are fractions of days (eg: 12 Noon = 0.5...half a day) Consequently... Each HOUR is 1/24 of a day Each MINUTE is 1/60 of an HOUR So....to do what you want, try this: =A1+B1/24+C1/(24*60) Remember to format as a date/time Does that help? *********** Regards, Ron XL2003, WinXP "ferde" wrote: Can anyone please help me with the following formating question? A B C D 3/27/2007 11 49 3/27/2007 11:49 I inherited a spreadsheet that has the hour and minute columns B&C formated as general. I tried to change the formating so I can get Column D to add the columns. I get an incorrect date .... instead it displays 5/26/2001 0:00 A2+B2+C2 should read 3/27/2007 11:49 Thank you in advance for your time and help Deb |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date time format question please
You're right again.... I don't know how I did that but I'm glad I wrote back
to you to ask. Is it generally ok to leave calcuation set on automatic? "Ron Coderre" wrote: It sounds like you've got Calculation set to Manual, with "Recalculate before Save" checked. From the Excel main menu: <tools<options<calculation tab Are those settings checked instead of "Automatic"? Does that help? *********** Regards, Ron XL2003, WinXP "ferde" wrote: 3/27/2007 11 49 3/27/07 11:49 11/28/2007 13 55 3/27/07 11:49 3/29/2007 20 10 3/27/07 11:49 5/30/2007 7 15 3/27/07 11:49 I noticed when I dragged the formula down the column it didnt actually compute correctly until I was ready to give up and so I just hit SAVE until tomorrow ....then boom it displayed correctly all the way down the column. I dont know if this is a fluke or not ? "ferde" wrote: Yes... it works perfect. Thank you so much I've been researching this myself all afternoon. "Ron Coderre" wrote: To Excel: €¢ Days are whole numbers €¢ Times are fractions of days (eg: 12 Noon = 0.5...half a day) Consequently... Each HOUR is 1/24 of a day Each MINUTE is 1/60 of an HOUR So....to do what you want, try this: =A1+B1/24+C1/(24*60) Remember to format as a date/time Does that help? *********** Regards, Ron XL2003, WinXP "ferde" wrote: Can anyone please help me with the following formating question? A B C D 3/27/2007 11 49 3/27/2007 11:49 I inherited a spreadsheet that has the hour and minute columns B&C formated as general. I tried to change the formating so I can get Column D to add the columns. I get an incorrect date .... instead it displays 5/26/2001 0:00 A2+B2+C2 should read 3/27/2007 11:49 Thank you in advance for your time and help Deb |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert date + time text format to date format | Excel Worksheet Functions | |||
| Date/Time Question | Excel Worksheet Functions | |||
| Time Format Question | Excel Worksheet Functions | |||
| how do I format cells to change date and time to just date | Excel Discussion (Misc queries) | |||
| Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions | |||
Hybrid Mode
