Data entry and working with time across midnight

[rlee1999]

I have a challenge for someone out there(maybe it's just me being challenged):
I am not going to be the end user on this and I am trying to avoid using vb if I can.

I work at a hospital and we are trying to track time through the emergency department. Everything is based on the time a patient walks through the door.
All of this is simple enough.

Door time(ColC), Triage time(ColD), Door to Triage(ColE), Doc with Patient Time(ColF), Door to Doc(ColG), etc.

How many minutes from door to triage(E23) = Triage time(D23) - Door time(C23) ....Data entry done as "07:45" Format [h]:mm

Now, when the times cross midnight I just add the date: Door Time "10/17/14 23:45", Triage Time "10/18/14 00:15" Format [h]:mm
Slows down data entry a little, but it's not that common...they can deal with it!

So that was all working wonderfully, except the data entry piece. Pausing to enter a colon for every measure of time was taking forever, much faster to enter the times as a 4 digit string "2345" instead of "23:45"

I started playing around with the data entry and functionality
Door Time(ColC) is now entered in a 4 digit string "2345", format 00\:00, so it looks like time, but it's not!
Inserted a new column in ColD, Formula =1*TEXT(C23,"00\:00"), Format [h]:mm ...now it's time
I did this after each Col where a "time" was entered. The result from the example above now lands in G23 =F23-D23, Format [h]:mm
I hid these new columns so the end user isn't confused by their existence.

It's working! I'm really not very good at this stuff, so I was kind of proud that I made it this far by myself:

So......what do I do when the time crosses midnight? I can't figure out how to work the date back into the equation.
I was thinking that I could format the new columns with date and time, it defaults to "1/0/0" and if ColF < ColD add a day? But I don't know how...Any help would be greatly appreciated!

~Robert

 

[Brian from Maui]

Try using the MOD function for calculating times past midnight

=MOD(Endtime-Starttime,1)

 

[mikerickson]

Unless you are going to add date to the entries, across midnight will be a problem. Unless you enter times like 28:13 when that is applicable.
If you are willing to accept that all intervals are less than 24 hours, Brian's MOD function will work.

As to the data entry, you could put this change event in the code module for the sheet in question.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 And (Not (Application.Intersect(.Cells, Range("A:A")) Is Nothing)) Then
            Application.EnableEvents = False
            .Value = Format(.Text, "00:00")
            Application.EnableEvents = True
        End If
    End With
End Sub

If you type "2314" in column A, it will be convert to the time 11:14 PM

You may have to adjust the Range("A:A") to something like Range("A:A,C:C,D:D") to match your situation.

 

[RickXL]

The best way to solve the problem would be to enter a full date/time stamp into every cell. What you need is a slick way to do that.

I know you didn't want a VBA solution but I have some code you could have that will let you enter a date stamp into several columns. All you have to do is select a cell in the relevant columns and a dialog pops up with the time now in it. (If the cell already had a time in it you can edit it.) The date and time can be altered if necessary.

Some checks are made on the validity of the time entered. The link is a screenshot.

https://onedrive.live.com/?cid=681493C8FF2672D3&id=681493C8FF2672D3!2531&v=3

Regards,