1/0/1900 10:18:48 AM Time problem

C

canix

I try to perform a calculation of the start time and finished time. Th
start time will be automatic put in by system using now() function an
the finish time will be put in by user. The problem is when user ente
the time the date is always 1/0/1900 10:18:48 AM, which make th
calculation negative. Is there anyway to overcome this problem
 
T

tobriant

You can format the cells as custom (e.g., m/d/yyyy h:mm AM/PM) have th
user enter data as 8/5/2005 9:05. This will store as 8/5/2005 9:05 AM
Have the output cell set as custom hh:mm:ss
 
C

canix

tobriant said:
You can format the cells as custom (e.g., m/d/yyyy h:mm AM/PM) have th
user enter data as 8/5/2005 9:05. This will store as 8/5/2005 9:05 AM
Have the output cell set as custom hh:mm:ss.
Click to expand...

But that would be too much for user who had to enter the date and tim
altogether. Is there anyway that can perform automatically where use
only need to enter the time
 
P

Peo Sjoblom

You realize that NOW() will update whenever the sheet is calculated, so if
the user type in a type eventually that time will be less than NOW()). If
you want to subtract 2 times and start time has a date and end does not you
can use

=B1-MOD(A1,1)

start time and date in A1 and end time in B1

--
Regards,

Peo Sjoblom

(No private emails please)
 
P

Peo Sjoblom

Oh dear! "type in a type" should be type in a time

--
Regards,

Peo Sjoblom

(No private emails please)
 
?

.

Canix said:
But that would be too much for user who had to enter the date and time
altogether. Is there anyway that can perform automatically where user
only need to enter the time.
Click to expand...

Time and date entry can be done more quickly using the shortcuts:
Date: CTRL-;
Time: CTRL-SHIFT-;

So to enter the date and time, just use:
<CTRL-;><space><CTRL-SHIFT-;>

Note: this only works if you want the current date and/or time to be
entered.
 
C

canix

I already got it figure it out. I am using a macro button to enter th
now time on the cell and have another cell convert it to text so i
would leave out the date. That way the finished time will calculat
correctly the the text cell.

I know it will need a hidden column for the converted Text time. Than
you all, I was hoping there's some easier way without using an extr
hidden column since my workbook is getting bigger and kinda runnin
slow
 
P

Peo Sjoblom

You can use a macro to just enter the time without the date

Sub testme()
With ActiveCell
.Value = Time
.NumberFormat = "h:mm:ss"
End With
End Sub




--
Regards,

Peo Sjoblom

(No private emails please)
 
Top