Importing and then converting time

  • This group was so helpful last time that I wondered if I might beg another question of you.


    I have enclosed a small sample worksheet of a report that is downloaded as a text file from mainframe at work.
    The time columns are not reflected as such as you can see. There are no colons in the downloaded text. I need to make a column that calculates the difference in time – discharge-arrival time (ColumnH –ColumnG) – to show the total amount of hours/minutes spent in my department. Some of the times cross midnight e.g. row 23 a child that arrived in the hospital at 9:35pm and left at 4:20am.


    Can you design something to change the time columns G and H to a format recognized by excel so I can perform calculations?


    Please make the instructions very simple – I’m not very good with this stuff. (The actual spreadsheets are very large with > 5000 rows.) Many thanks in advance.

  • Sample Missing


    Oops! :D


    I think you forgot to attach/enclose your sample worksheet.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • One Additional Column Only


    If you just wanted to have the Duration column, then use dangelor's duration formula. Copy the formula in J2 and then select each reference to J2 in the Duration formula and paste. Then copy the formula in I2 and then select each reference to I2 in the Duration formula and paste.


    =IF(TIMEVALUE(LEFT(G2,LEN(G2)-2) & ":" & RIGHT(G2,2))<TIMEVALUE(LEFT(H2,LEN(H2)-2) & ":" & RIGHT(H2,2)),TIMEVALUE(LEFT(H2,LEN(H2)-2) & ":" & RIGHT(H2,2))-TIMEVALUE(LEFT(G2,LEN(G2)-2) & ":" & RIGHT(G2,2)),TIMEVALUE(LEFT(H2,LEN(H2)-2) & ":" & RIGHT(H2,2))-TIMEVALUE(LEFT(G2,LEN(G2)-2) & ":" & RIGHT(G2,2))+1)


    Makes for a longer looking formula but unless there is some reason why you want the Arrival Time and Discharge Time to be displayed in the h:mm format, then 1 column will do the job.


    I don't know if you create a spreadsheet for each day?


    Quote from SteveinLA

    small sample worksheet of a report that is downloaded as a text file from mainframe

    Is the report already in a spreadsheet format?


    Perhaps your mainframe report can add a calculated field to calculate the duration before you download the report. Just a thought.


    If you wanted to copy the Duration formula in column O down 5000 rows or more and then paste your mainframe report data into columns A to N, I would modify the Duration formula so that the formula will not calculate unless both the Arrival Time and Discharge Time for that particular row is not blank.


    =IF(AND(ISBLANK(G2),ISBLANK(H2)),"",IF(TIMEVALUE(LEFT(G2,LEN(G2)-2) & ":" & RIGHT(G2,2))<TIMEVALUE(LEFT(H2,LEN(H2)-2) & ":" & RIGHT(H2,2)),TIMEVALUE(LEFT(H2,LEN(H2)-2) & ":" & RIGHT(H2,2))-TIMEVALUE(LEFT(G2,LEN(G2)-2) & ":" & RIGHT(G2,2)),TIMEVALUE(LEFT(H2,LEN(H2)-2) & ":" & RIGHT(H2,2))-TIMEVALUE(LEFT(G2,LEN(G2)-2) & ":" & RIGHT(G2,2))+1))

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Dangelor fix..


    Thanks for taking the time to work this out for me.
    I played with the sample you sent me..the only bug I see is if someone arrives late in the day and is discharged between 00:00 and 01:00 - these times are not reflected properly. Do you have an idea to fix?
    Thanks :?

  • Quick answer, if columns G and H are formatted as General, then a time such as 040 is entered as 040 becomes 40.


    Try formatting the two columns as Text. Then when 040 is entered, it remains as 040.


    If you're copy and pasting the values over from another worksheet, make sure you paste the values only (Edit, Paste Special..., Values, OK).


    Let me know if that doesn't work.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!