Hi there
I have data showing as '1d 5h 30m' I wish to display is as dd:hh:mm (01:05:30)
So far I have managed to use the substitute formula to strip it down to show 1:5:30.
It seems to work to an extent some other examples include
8h becomes 8:
4d 1h becomes 4:1:
The formula I have used is =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C9,"d",":")," ",""),"h",":"),"m","")
Thanks
Last edited by ctp91; 05-21-2019 at 07:13 AM.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
have done
that are many variations to take into account, so it is a longer formula
Formula:
Please Login or Register to view this content.
the outcome of this formula is a number that can be formatted as day and hours format
also the cell format is changed to dd:hh:mm so days are also shown in time like format.
see attachment for details
Hey! this works for some of the cells, however when it comes to 8h or 5m it displays the duration as 00:00:00
Try: =SUMPRODUCT(VALUE(IFERROR(MID(0&A16,SEARCH({"d","h","m"},0&A16)-2,2),{0,0,0})),{1440,60,1})/1440
Remember to format the cells dd:hh:mm
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks