Hey guys,
I'm hoping someone can help convert times I have stored as text into time serial number format.
The syntax the data is supplied is: 1 dy 12 hr 50 min 13 sec
Unfortunately I can't use LEFT/MID/RIGHT formulas to pull the numbers out because the data is inconsistent in length. See a few different examples below:
I would like the output to be in time/date serial number format.
Please Login or Register to view this content.
Thanks for your help!
Adam
not so, that is (probably) exactly what you use, you just need to tell it where to "break"Unfortunately I can't use LEFT/MID/RIGHT formulas to pull the numbers out because the data is inconsistent in length.
Let me take a look and see what I can do. In the mean time, can you give some sample answers please?
Also, will the days always only be 1 digit?
Last edited by FDibbins; 01-13-2016 at 01:14 AM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
OK, so I did not use left/mid too much, but it is still inthere
1 dy 12 hr 50 min 13 sec 2 min 43 sec 2 min 40 sec 6 hr 1 min 36 sec 43 sec 3 hr 48 min 17 sec 11 hr 30 min 8 sec 8 hr 14 sec 1 dy 7 hr 31 min 41 sec 6 hr 1 min 36 sec 3 hr 48 min 17 sec 11 hr 30 min 8 sec [BGCOLOR=#F2F6F8]8 hr 14 sec[/BGCOLOR]
copied downB1=IF(LEN(A1)<7,TIME(0,0,LEFT(A1,3)),IF(ISERROR(SEARCH("dy",A1,1)),TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"hr",":"),"min",":"),"sec","")," ","")),LEFT(A1,2)*24+TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,6,99),"hr",":"),"min",":"),"sec","")," ",""))))
Hey there,
Thanks for looking into this for me! Much appreciated.
This isn't quite working as I expect it should. See the results below for each:
ORIGINAL AS TEXT OUTPUT 1 dy 12 hr 50 min 13 sec 24 days 12 hours 50 minutes 13 seconds 2 min 43 sec 00 days 02 hours 43 minutes 00 seconds 2 min 40 sec 00 days 02 hours 40 minutes 00 seconds 6 hr 1 min 36 sec 00 days 06 hours 01 minutes 36 seconds 43 sec 00 days 00 hours 00 minutes 43 seconds 3 hr 48 min 17 sec 00 days 03 hours 48 minutes 17 seconds 11 hr 30 min 8 sec 00 days 11 hours 30 minutes 08 seconds 8 hr 14 sec 00 days 08 hours 14 minutes 00 seconds 1 dy 7 hr 31 min 41 sec 24 days 07 hours 31 minutes 41 seconds 6 hr 1 min 36 sec 00 days 06 hours 01 minutes 36 seconds 3 hr 48 min 17 sec 00 days 03 hours 48 minutes 17 seconds 11 hr 30 min 8 sec 00 days 11 hours 30 minutes 08 seconds 8 hr 14 sec 00 days 08 hours 14 minutes 00 seconds
ISSUES:
- It's categorising 1 day as 24 days in the first entry
- It's putting some numbers into the wrong category (e.g. treating seconds as minutes when minutes didn't exist in the last entry)
Could you please investigate?
Adam
Originally Posted by FDibbins
Please Login or Register to view this content.Good catch! You stole my thunder. Other potential issues:
3. I think the formula would interpret just "13 hr", "13 min" and "13 dy" as 13 seconds.
4. The formula returns #VALUE for just "9 sec" or less.
Simply remove "*24" to fix the first problem.
I'll let FDibbins work on the other problems.
Last edited by joeu2004; 01-13-2016 at 03:38 AM. Reason: Add to #3
If you use Custom Formatting like this...
dd "days" hh "Hours" mm "Minutes" ss "Seconds"
Then you will still have a real day/time answer. Otherwise you will have a text answer that you will not be able to use in further calcs
@ Joeu, good catch on the single-digit seconds, this fixes that (see bolded)...
=IF(LEN(A1)<7,TIME(0,0,LEFT(A1,2)),IF(ISERROR(SEARCH("dy",A1,1)),TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"hr",":"),"min",":"),"sec","")," ","")),LEFT(A1,2)*24+TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,6,99),"hr",":"),"min",":"),"sec","")," ",""))))
Adam was trying to demonstrate the many errors in your results.
BTW, generally, we cannot use "dd" to format number of days. "dd" really formats day of the month. By coincidence, it seems to work for 31 days or less because Jan has 31 days. But "dd" formats 32 as 1 because 32 is interpreted as 1 Feb 1900.
The changes do not address the major issues that Adam and I pointed out. The following table demonstrates the behavior of your formula for all combinations of dy hr min sec. (Think of it as a 4-bit binary value.)
Aside.... I am unable to create tables, as you and Adam did. I think I might use features that this forum does not support. But I'm sure it used to work in the past.Please Login or Register to view this content.
Column B contains your revised formula. Column C contains the formula shown. Alternatively, column C could contain formulas of the form =B1 formatted a d:hh:mm:ss. But as I noted above, the "d" incorrectly formats days as 17 because 48 is interpreted as 17 Feb 1900.
The point is: your formula returns incorrect in most cases.
As I noted previously, the number of days (48) can be corrected simply by omitting "*24" in your formula.
But even with that correction, your formula returns correct results only in B1, B6, B7, B12, B14 and B15.
Last edited by joeu2004; 01-13-2016 at 05:05 AM. Reason: cosmetic; simplify time format; B1 is correct
The following seems to work for all combinations of dy hr min sec. I assume that hr is 0 to 23, and min and sec are 0 to 59 [1]. I make no assumptions about the number of days.
[1] Actually, min and sec can be up to 99, as long as the converted time including hr is less than 24 hours.
The formula in B2 is as follows, copied into B3:B16 and formatted as Custom d:hh:mm:ss.Please Login or Register to view this content.
The formula in C2 is =INT(B2), copied into C3:C16.Please Login or Register to view this content.
It displays the correct number of days. As I have noted previously, the "d" specifier in the format for column B is not reliable for formatting number of days. It works in these examples only by coincidence.
PS.... If you do not require Excel 2003 compatibility, the formula can be simplified as follows.
Please Login or Register to view this content.
Last edited by joeu2004; 01-13-2016 at 06:02 AM. Reason: correct C2; PS
Thanks, this looks like it's returning correct results for all of the results required.The formula in B2 is as follows, copied into B3:B16 and formatted as Custom d:hh:mm:ss.
Please Login or Register to view this content.
A few questions:
- Can you confirm it works for single and double digit days, months, minutes and seconds?
- Can you also confirm it works when any/multiple of these attributes are missing completely?
- Finally, is there a way to correct the days issue you described? It doesn't matter to me if it returns serial numbers in Feb 1900. I just want to be able to perform calculations on the data, hence the serial numbers.
Thanks for all your help, guys!
Adam,
it looks like joeu2004 is offline at the moment, but you can answer those questions yourself (well, 1 and 2 anyway) by trying it out with appropriate test data. For number 3, you can format the result as a date and time (e.g. dd/mm/yyyy hh:mm:ss, or whatever style you want), and it will give you a date that is in January 1900 (or a later month, depending on the number of days).
Hope this helps.
Pete
posted wrong formula
Last edited by hemesh; 01-13-2016 at 09:03 PM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
wow seriously embarrased, I normally test my formulas way better than that. I think this will give you what you want...
1 dy 12 hr 50 min 13 sec 1 days 12 Hrs 50 Mins 13 Secs 2 min 43 sec 0 days 0 Hrs 2 Mins 43 Secs 2 min 40 sec 0 days 0 Hrs 2 Mins 40 Secs 6 hr 1 min 36 sec 0 days 6 Hrs 1 Mins 36 Secs 43 sec 0 days 0 Hrs 0 Mins 43 Secs 3 hr 48 min 17 sec 0 days 3 Hrs 48 Mins 17 Secs 11 hr 30 min 8 sec 0 days 11 Hrs 30 Mins 8 Secs 8 hr 14 sec 0 days 8 Hrs 0 Mins 14 Secs 1 dy 7 hr 31 min 41 sec 1 days 7 Hrs 31 Mins 41 Secs 6 hr 1 min 36 sec 0 days 6 Hrs 1 Mins 36 Secs 3 hr 48 min 17 sec 0 days 3 Hrs 48 Mins 17 Secs 11 hr 30 min 8 sec 0 days 11 Hrs 30 Mins 8 Secs 8 hr 14 sec 0 days 8 Hrs 0 Mins 14 Secs 11 dy 12 hr 50 min 13 sec 11 days 12 Hrs 50 Mins 13 Secs
B1=IF(ISNUMBER(SEARCH("dy",A1)),LEFT(A1,2),0)&" days "&IFERROR(MID($A1,SEARCH("hr",$A1,1)-IF(MID(A1,3,2)="hr",2,3),2),0)&" Hrs "&IFERROR(MID($A1,SEARCH("min",$A1,1)-IF(MID(A1,3,3)="Min",2,3),2),0)&" Mins "&IFERROR(MID($A1,SEARCH("sec",$A1,1)-3,2),0)&" Secs"
If you want the actualy time/number, try this variation...
1 dy 12 hr 50 min 13 sec 1 days 12 Hrs 50 Mins 13 Secs 2 min 43 sec 0 days 0 Hrs 2 Mins 43 Secs 2 min 40 sec 0 days 0 Hrs 2 Mins 40 Secs 6 hr 1 min 36 sec 0 days 6 Hrs 1 Mins 36 Secs 43 sec 0 days 0 Hrs 0 Mins 43 Secs 3 hr 48 min 17 sec 0 days 3 Hrs 48 Mins 17 Secs 11 hr 30 min 8 sec 0 days 11 Hrs 30 Mins 8 Secs 8 hr 14 sec 0 days 8 Hrs 0 Mins 14 Secs 1 dy 7 hr 31 min 41 sec 1 days 7 Hrs 31 Mins 41 Secs 6 hr 1 min 36 sec 0 days 6 Hrs 1 Mins 36 Secs 3 hr 48 min 17 sec 0 days 3 Hrs 48 Mins 17 Secs 11 hr 30 min 8 sec 0 days 11 Hrs 30 Mins 8 Secs 8 hr 14 sec 0 days 8 Hrs 0 Mins 14 Secs 11 dy 12 hr 50 min 13 sec 11 days 12 Hrs 50 Mins 13 Secs
B1=IF(ISNUMBER(SEARCH("dy",A1)),LEFT(A1,2),0)&" days "&IFERROR(MID($A1,SEARCH("hr",$A1,1)-IF(MID(A1,3,2)="hr",2,3),2),0)&" Hrs "&IFERROR(MID($A1,SEARCH("min",$A1,1)-IF(MID(A1,3,3)="Min",2,3),2),0)&" Mins "&IFERROR(MID($A1,SEARCH("sec",$A1,1)-3,2),0)&" Secs"
(same as before)
C1=IF(ISNUMBER(SEARCH("dy",A1)),LEFT(A1,2),0)+TIMEVALUE(IFERROR(MID($A1,SEARCH("hr",$A1,1)-IF(MID(A1,3,2)="hr",2,3),2),0)&":"&IFERROR(MID($A1,SEARCH("min",$A1,1)-IF(MID(A1,3,3)="Min",2,3),2),0)&":"&IFERROR(MID($A1,SEARCH("sec",$A1,1)-3,2),0))
this is an actual value, formatted as dd "days" hh "Hours" mm "Minutes" ss "Seconds"
D1 is the same formula, just formatted as general
Yes, within the limits that I specified (e.g. hr is 0 to 23). I tested single and double digits in all 15 combinations.
(You could easily do the same.)
Yes, if I understand the question. The table that I provided demonstrates all combinations of missing and present dy, hr, min and sec attributes.
However, that was just my guess. You never explained or demonstrated all the possible forms of input. If there is a form that I have not covered, it would behoove you to test it and/or tell us what you have not mentioned yet.
As I stated repeatedly in this thread, we cannot use the "d" specifier to display number of days greater than 31.
But that does not obviate the possibility of including larger number of days in the serial number itself. It is just a number.
If that is all that you require -- not displaying the elapsed days -- you should have no problem with the formulas that I presented.
(And if you are certain that the number of days will never exceed 31, you are free to use the "d" specifier.)
-----
Perhaps it will help to have a general review of how Excel stores elapsed days/time and date/time.
In general, elapsed days/time and date/time are stored as a number of days and fractional days. That is, the integer part is a number of days; the fractional part is the (remaining) time.
We can interpret the integer part as the number of elapsed days in a time calculation, or as the number of days since 31 Dec 1899 in a date.
The two are indistinguishable internally. The difference is simply a matter of interpretation of the numeric representation.
(Excel displays 31 Dec 1899 as 0 Jan 1900. We cannot enter either of those dates manually.)
However, the "d" specifier always uses the second interpretation (number of days since 31 Dec 1899).
And I don't know of any format specifier that uses the first interpretation (number of elapsed days).
AFAIK, the only reliable way [1] to display number of elapsed days and time is a text formula like the one that I demonstrated in message #8, to wit:
=INT(B2) & TEXT(B2," hh:mm:ss")
But Excel does not recognize that as a text representation of elapsed days/time. So we cannot reference that cell in another cell that performs arithmetic calculations.
So if you also want to display the elapsed days/time reliably, you might put the serial number calculation in one cell and the "formatted" display (text formula) of the serial number in a parallel cell.
-----
[1] Technically, =INT(B2)&TEXT(B2," hh:mm:ss") is not entirely reliable because Excel rounds fractional seconds. But I think it should be sufficient for your purposes because the serial numbers are calculated from the input form, and they are accurate to the second. "Famous last words".
Last edited by joeu2004; 01-13-2016 at 11:30 PM. Reason: cosmetic
Another approach may be copy paste below in B1 and then hit enter
below formula gives actual time serials
=IF(ISNUMBER(SEARCH("dy",A1)),LEFT(A1,2),0)+SUMPRODUCT(IFERROR(--(TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",10)),(SEARCH({"hr","min","sec"},SUBSTITUTE(" "&A1," ",REPT(" ",10))))-18,9))),0)/{24,1440,86400})
works for 2 digit of day and 5 digits of hour, min, sec each
in case you want to apply formatting then use [h]:mm:ss that shows you exceeding hours in format of more than 24 hours
lets say instead of 24 hours you had 25 hours then it shows 25 and so on and hours of the days will be added to the hour section
Last edited by hemesh; 01-14-2016 at 10:42 AM.
Thanks everyone for your valued contributions!
Looks like we've got quite a few solutions which all work, with different formatting options depending on the output needed.
Love you all! <3
@ Adam, you are welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks