LinkBack URL
About LinkBacks
hello
i have an issue with exported data from a Cognos server
it gives me time format in days:hours:mins but it displays it as 14 11:06 (14 days, 11 hours, 6 mins)
is there a formula that can be used to convert to [h]:mm
i have attached an example
hello
i have an issue with exported data from a Cognos server
it gives me time format in days:hours:mins but it displays it as 14 11:06 (14 days, 11 hours, 6 mins)
is there a formula that can be used to convert to [h]:mm
i have attached an example
Last edited by andrewmo; 09-21-2012 at 03:38 PM.
Do you want the days converted to hours? So for 3 days, 5 hours 15 minutes, are you looking for 5:15, or 77:15?
Brendan.
__________________________________________________________________________________________________
Things to consider:
1) You can thank any poster by clicking the * at the left of a helpful post.
2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
yes, hours please 77:15
And do you need to keep that data in the order that it's in?
not really, but it would be nice
This formula works except when a single value appears (i.e. 42). Is that 42 days? hours? minutes?
=IF(ISNUMBER(SEARCH(" ",C2)),TRIM(LEFT(C2,2))+TIMEVALUE(RIGHT(C2,LEN(C2)-SEARCH(" ",C2))),TIMEVALUE(C2))
Format cells as [h]:mm or [hh]:mm
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
oh yeah, good point, i believe when it's just a single number it's suppose to be minutes
thanks, i will give it a try!
Lots of things would be niceThis is going to involve some grunt work - once I figure out the best way to do this, I'll change a column, and you can do the rest yourself. There's no formula as such that I'm aware of, but bear with me.....
EDIT: Orrrrrr, someone who really knows their stuff could come along and show us the proper way to do it. Cheers, ChemistB.
Yep, that did it. Thank you BB1972 and ChemistB!!!
LOL, save it for ChemistB, in fairness! Glad you're sorted.
If the whole numbers are minutes
=IF(ISNUMBER(SEARCH(":",C2)),IF(ISNUMBER(SEARCH(" ",C2)),TRIM(LEFT(C2,2))+TIMEVALUE(RIGHT(C2,LEN(C2)-SEARCH(" ",C2))),TIMEVALUE(C2)),TEXT(C2/(24*60),"hh:mm")+0)
Glad to help. Thanks Brendan.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks