Excel Time formatting problem

[shearbec]

Has anyone ever had the problem where you format cells to specifically be time, no date and it still force that date to be associated? For example, I've formatted a cell to be hh:mm and when you enter in a time, it now defaults the date as what ever it'd like to pick, ie 1/9/04 1:23 AM

Excel 97: Just wondering if it could be a corruption problem.

Any help would greatly be appreciated.

 

[SkipVought]

Hi,
Excel stores Dates as integer values. Zero is 01/00/1900, a non-existant date. (you can see this if you format your cell as General. Today is 37285.

But Time is stored to the right of the decimal, where .0 is 12:00 AM and .9999 is 11:59:51 PM.

Hope this helps
Skip,
metzgsk@voughtaircraft.com

 

[Sypher2]

You either need to enter the time as decimals like Skip says, or you need to enter in the time with a colon. You can't type "130" for 1:30 AM. You need to type "1:30".

 

[SkipVought]

shearbec,

    Dim dTime As Date, sT As String
    dTime = Now - Int(Now)
    sT = "Time :"
    MsgBox sT & dTime

works just fine. Skip,
metzgsk@voughtaircraft.com

 

[jonsi]

I know it's slightly off shearbec's original question but you can enter 4 digits eg 1445 for 2:45 and have them output in time formatted Cell.

Input Cell A1 has 1445 (no decimals)
Output Cell B1 has the following IF statement
=IF(A1<1000,(LEFT(A1,1)*(1/24)+RIGHT(A1,2)*(1/24/60)),(LEFT(A1,2)*(1/24)+RIGHT(A1,2)*(1/24/60)))

cheers
Jonsi