Date time format question please

[ferde]

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

 

[Ron Coderre]

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]

Yes... it works perfect. Thank you so much I've been researching this myself
all afternoon.

 

[ferde]

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 ?

 

[Ron Coderre]

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]

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]

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

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

XL2003, WinXP

 

[ferde]

OK.. Thanks again for all of your help.
Deb

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

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

XL2003, WinXP