time sorting

Posted by Jason on November 27, 2000 6:02 AM

I have a spreadsheet that the night warehouse uses to record when they finish pulling an invoice. The manager would like to know when the last invoice was finished. The warehouse works from 8:00pm to about 4:00am. When sorting the spreadsheet it treats the times as military time therefore the last time they worked (around 4:00am) is in the middle of the sort. Is there a way to create a formula that will look at the list and let us know what the last time in the morning was used. Thanks.

Posted by Ben O. on November 27, 2000 9:49 AM

As it is now, Excel has no way of knowing that your AM times are on a later day than your PM times. It assumes they're on the same day. Perhaps you could instruct the person who enters the times to add 24 hours to times past midnight. 4:00 AM would be 28:00, for instance. Excel will store the date at 01/01/00 4:00 A.M. Since it stores other times as 01/00/00, it will know that your A.M. times are for the following day.

-Ben

Posted by Celia on November 28, 2000 12:26 AM

Jason
The times could be entered by running the following macro (which could be assigned to a button). This should make it easier to record the times and they could also be sorted.
The macro assumes that the times are to be entered in column A (column 1). just change the column reference in the macro to fit your requirements.

Sub EnterTime()
If Selection.Column = 1 Then
With Selection
.Value = Now
.NumberFormat = "h:mm AM/PM"
End With
Else
MsgBox "To enter the time, first select a cell in Column A."
End If
End Sub

If you want to find the latest time without sorting column A, use the following formula :-
=MAX(A:A)

Celia

Posted by Jason on November 28, 2000 1:05 PM

{=MAX(IF(($P$5:$P$233)&LT;0.5,$P$5:$P$233))}