Date time format question please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Yes... it works perfect. Thank you so much I've been researching this myself
all afternoon.
 
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 ?
 
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
 
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?
 
Unless your workbook takes abusively long to recalculate, you should
generally leave Calculation set at Automatic.

***********
Regards,
Ron

XL2003, WinXP
 
OK.. Thanks again for all of your help.
Deb

Ron Coderre said:
Unless your workbook takes abusively long to recalculate, you should
generally leave Calculation set at Automatic.

***********
Regards,
Ron

XL2003, WinXP
Click to expand...
 
Back
Top