Converting a Time Extracted from Data as hh:mm problem

[boabmccann]

Good morning everyone,

First post here for me, used forum a lot for answers but never came across one i cant see a definitive answer for.

When i extract data from software used to log time on a site it is pulling as for example 7h 42m and so on, so it has the hour in numbers followed with the H and minutes in numbers followed by M.

Is there any easy way i can have this just simply changed to hours and minutes so i could total up all the pulled data, it is a weekly timesheet so normally pulling 5 days worth.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[ByteMarks]

Maybe a formula like this with the cell formatted as Time

Please Login or Register  to view this content.

[HansDouwe]

Maybe a formula like this with the cell formatted as Time

Please Login or Register  to view this content.

I think there need 1 more space (after the h) in the formula:

Please Login or Register  to view this content.

[ByteMarks]

Timevalue takes care of it I think.

[Pete_UK]

It might also be wise to change the "m" to ":0" in the final substitute, to avoid confusion about h:m and m:s

Hope this helps.

Pete

[boabmccann]

demo11.xlsx

attachment added

[boabmccann]

Thankyou have updated profile and now attached an excel sheet (stripped out sensitive info)

[boabmccann]

I just really want to capture the time per user and then probably an overall time of them all combined.

To hopefully just display it as for example 36:13 if it was showing as 1d 12h 13m as an example

[AliGW]

Your profile still doesn't tell us which Excel PRODUCT you are using.

[boabmccann]

i think that is it now, apoligies

[AliGW]

Yes - that's much better - thanks.

Have you tried the solutions offered? If so, does any of them work for you?

[Pete_UK]

Assuming that the times in column I are correct (the first two are out by 1 minute), you can use this formula in N6:

=IF(COUNTIF(I6,"*d*"),LEFT(I6,FIND("d",I6)-1)+TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(I6,4,10),"h",":"),"m",":0")),TIMEVALUE(SUBSTITUTE(SUBSTITUTE(I6,"h",":"),"m",":0")))

Use a custom format of [h]:mm, and then copy down to the bottom of your data. Note that the final result gives a #VALUE error, because I40 contains a "w". Rather than complicate the formula even more, you can just insert an autosum in that cell.

Hope this helps.

Pete

[boabmccann]

thanks for the information now doing what i had hoped for

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[HansDouwe]

I don't understand why you have a text 10h 43m for example.
It is also possible to calculate this direct by subtracting the start from the stop time and format the time in the desired format.

You can calculate with the results.

See examples in attachment.

[Pete_UK]

I've just discovered that my formula does not work properly when there are no hours in column I, e.g. I20, I22, or I27. Here, the minutes are shown as hours, although the sub totals remain correct.

One way to overcome this is to use the time and date values directly, and to use this formula in O6:

=F6-E6

Format the cell with a custom format of [h]:mm and copy down. This is how I discovered that many of your time differences are out by 1 minute. There is also no value (0:00) reported for the subtotals, but it is quite easy to insert a SUM() function with appropriate ranges wherever that occurs.

Hope this helps.

Pete