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

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


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393333

You can format the cells as custom (e.g., m/d/yyyy h:mm AM/PM) have the
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.


--
tobriant
------------------------------------------------------------------------
tobriant's Profile: http://www.excelforum.com/member.php...o&userid=25155
View this thread: http://www.excelforum.com/showthread...hreadid=393333

tobriant Wrote:
You can format the cells as custom (e.g., m/d/yyyy h:mm AM/PM) have the
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.

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.


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393333

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)


"canix" wrote in
message ...

tobriant Wrote:
You can format the cells as custom (e.g., m/d/yyyy h:mm AM/PM) have the
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.

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.


--
canix
------------------------------------------------------------------------
canix's Profile:
http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393333

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

--
Regards,

Peo Sjoblom

(No private emails please)


"Peo Sjoblom" wrote in message
...
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)


"canix" wrote in
message ...

tobriant Wrote:
You can format the cells as custom (e.g., m/d/yyyy h:mm AM/PM) have the
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.

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.


--
canix
------------------------------------------------------------------------
canix's Profile:
http://www.excelforum.com/member.php...o&userid=25962
View this thread:
http://www.excelforum.com/showthread...hreadid=393333

Canix wrote:
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.

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.

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

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


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393333

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)


"canix" wrote in
message ...

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

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


--
canix
------------------------------------------------------------------------
canix's Profile:
http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393333