Convert time string/text to time (calculable)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a column of data which consists of strings such as follows:
Row 1: 46m 06s.
Row 2: 15d 5h 09m 33s
Row 3: 17h 24m 59s
Etc
I want to convert these strings to number or time format so I can perform calculations on them, but the current format makes various formula challenging to use. Ideas?
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2016
add a comment |
I have a column of data which consists of strings such as follows:
Row 1: 46m 06s.
Row 2: 15d 5h 09m 33s
Row 3: 17h 24m 59s
Etc
I want to convert these strings to number or time format so I can perform calculations on them, but the current format makes various formula challenging to use. Ideas?
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2016
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:11
add a comment |
I have a column of data which consists of strings such as follows:
Row 1: 46m 06s.
Row 2: 15d 5h 09m 33s
Row 3: 17h 24m 59s
Etc
I want to convert these strings to number or time format so I can perform calculations on them, but the current format makes various formula challenging to use. Ideas?
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2016
I have a column of data which consists of strings such as follows:
Row 1: 46m 06s.
Row 2: 15d 5h 09m 33s
Row 3: 17h 24m 59s
Etc
I want to convert these strings to number or time format so I can perform calculations on them, but the current format makes various formula challenging to use. Ideas?
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2016
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2016
edited Mar 5 at 23:30
Rox
asked Mar 5 at 5:13
RoxRox
112
112
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:11
add a comment |
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:11
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:11
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:11
add a comment |
3 Answers
3
active
oldest
votes
Public Function convert_text_to_interval(interval As String) As Double
Dim temp() As String, tmp As Variant
On Error GoTo error_handler
temp = Split(interval)
For Each tmp In temp
Select Case Right(Trim(tmp), 1)
Case "d"
convert_text_to_interval = convert_text_to_interval + Val(tmp)
Case "h"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 24#
Case "m"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 1440#
Case "s"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 86400#
End Select
Next
Exit Function
error_handler:
convert_text_to_interval = 0
End Function
The function must be inserted into a common module (not to a sheet or class module!).
After insertion it will be available in formula master in User-defined functions section. Or it can be inserted directly (for example, as =convert_text_to_interval(A1)
).
The spaces between interval parts are compulsory (1h30m
not allowed, it will be treated as 1m
, see below).
The unit letter must be adjacent to a value (1 h 30 m
not allowed, it will be treated as 0h 0m
, see below).
The parts without correct unit letter are ignored. If unit part contains more than 1 letter, the last one is used, all another are ignored (1hm
== 1m
). All non-digit symbols except the last unit letter are ignored.
Multiple parts with the same unit (1h 15m 30m
== 1h 45m
) are allowed.
Fractional values (decimal separator - dot .
) are allowed (1.5h
). Negative values are allowed (2h -20m
== 1h 40m
).
NumberFormat for a cell where this function is used can be both number and time (in last case recommended format is Cell.NumberFormat = "[h]:mm:ss" - it will not cut hours over a day, for example, 15d 5h 09m 33s
will be shown as 365:09:33
, not 5:09:33
as for "h:mm:ss" format).
Include an example that how to use the Function for example=convert_text_text_to_interval(Cell refe)
.
– Rajesh S
Mar 5 at 6:09
Your code needs this corrections,,tmp As Variant
instead ofString
andCase code should Case "h"
instead ofCase 'h'
. Also what about the Format of the Formula Cell !!
– Rajesh S
Mar 5 at 6:21
Case
code shouldCase "h"
instead ofCase 'h'
Thanks, edited.tmp As Variant
instead ofString
Edited too. what about the Format of the Formula Cell As user needed (maybe number, maybe time).
– Akina
Mar 5 at 6:28
For this line ,,dim temp() as string, tmp
it shoulddim temp() as string, tmp as Variant
. And for the first example if Formula cell is Number theAns is Zero
,, if Time the is12:07:00 AM
,, I think it should01:46:06 AM
instead.
– Rajesh S
Mar 5 at 6:41
@RajeshS And for the first example if Formula cell is Number the Ans is Zero,, if Time the is 12:07:00 AM ,, I think it should 01:46:06 AM instead. I cannot understand this phrase. And whyAM
- it is interval, not time.
– Akina
Mar 5 at 6:55
|
show 3 more comments
Parses a String of text for 'd' 'h' 'm' 's' values and converts to time.
This will not replace the string values. Instead, use this formula to create a helper column for the time arithmetic.
- To enable the display of over 24 hours in in the time formula cells, use the
[h]:mm:ss
time format. - All value & letter pairs in the time string are optional. If the letter is present, the value must be attached.
- No space between value and unit letter
3h
not3 h
(not: 3 space h) - Separate one pair from the next with a space.
3d 2h
(3d space 2h)
This is an error:3d2h
(if it has two digits, like the 11 in3d11h
, it should be ok). - The letters are case insensitive (e.g. 'h' or 'H').
Multiline formatted formula: Paste directly into the formula bar
to avoid splitting the formula over multiple rows.
=VALUE( IFERROR( LEFT( I11, SEARCH( "d", I11) - 1),0) * 24 +
IFERROR( IFERROR( MID( I11, SEARCH( "h", I11) - 2, 2), MID( I11, SEARCH( "h", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "m", I11) - 2, 2), MID( I11, SEARCH( "m", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "s", I11) - 2, 2), MID( I11, SEARCH( "s", I11) - 1, 1)), 0))
The value I11
is the cell with the time string (search and replace).
- Or create (insert) helper column.
- Insert new row.
- Paste formula in new row in helper column.
- Cut cell I11 and paste in new row's time string column (assumes I11 is not referenced by an existing formula).
- Copy the contents of original I11 back to the formula bar of I11 so the relative addresses are not moved back, just the contents.
It's working,, now you get 10 ☺
– Rajesh S
Mar 5 at 7:23
add a comment |
Your issue can be solved by using few Helper Columns also:
How it works:
- Sample data in Range
A4:E29
.
D, H, M & S
indicatesDay, Hour, Minute &
.
Second
Formula in B4 & fill down:
=VALUE(IFERROR(LEFT(A4,SEARCH("d",A4)-1),0)*24)/24
Formula in C4 & fill down:
=IF(ISNUMBER(SEARCH(C$3,$A4)),MID($A4,IF(SEARCH(C$3,$A4)<5,1,SEARCH(C$3,$A4)-2),IF(SEARCH(C$3,$A4)<5,SEARCH(C$3,$A4)-1,2)),0)*1
Formula in D4 & fill down:
=IF(ISNUMBER(SEARCH(D$3,$A4)),MID($A4,IF(SEARCH(D$3,$A4)<5,1,SEARCH(D$3,$A4)-2),IF(SEARCH(D$3,$A4)<5,SEARCH(D$3,$A4)-1,2)),0)*1
Formula in E4 & fill down:
=IF(ISNUMBER(SEARCH(E$3,$A4)),MID($A4,IF(SEARCH(E$3,$A4)<5,1,SEARCH(E$3,$A4)-2),IF(SEARCH(E$3,$A4)<5,SEARCH(E$3,$A4)-1,2)),0)*1
Final Formula in F4:
=IF(LEN(A4)>1,B4+C4/24+D4/(24*60)+E4/(24*60^2),"")
N.B.
- Adjust cell references in the Formula as
needed. - Cell Format for Range
A4:E29
is
GENERAL
. - Cell Format for Range
F4:F29
is
[h]:mm:ss
. - You can hide Helper Columns as soon you
get the job done.
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:09
@Rox,, glad to help you,, I've tried to include as many example I can,, follow instructions properly, you also find is working. ☺
– Rajesh S
Mar 5 at 10:11
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1411316%2fconvert-time-string-text-to-time-calculable%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Public Function convert_text_to_interval(interval As String) As Double
Dim temp() As String, tmp As Variant
On Error GoTo error_handler
temp = Split(interval)
For Each tmp In temp
Select Case Right(Trim(tmp), 1)
Case "d"
convert_text_to_interval = convert_text_to_interval + Val(tmp)
Case "h"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 24#
Case "m"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 1440#
Case "s"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 86400#
End Select
Next
Exit Function
error_handler:
convert_text_to_interval = 0
End Function
The function must be inserted into a common module (not to a sheet or class module!).
After insertion it will be available in formula master in User-defined functions section. Or it can be inserted directly (for example, as =convert_text_to_interval(A1)
).
The spaces between interval parts are compulsory (1h30m
not allowed, it will be treated as 1m
, see below).
The unit letter must be adjacent to a value (1 h 30 m
not allowed, it will be treated as 0h 0m
, see below).
The parts without correct unit letter are ignored. If unit part contains more than 1 letter, the last one is used, all another are ignored (1hm
== 1m
). All non-digit symbols except the last unit letter are ignored.
Multiple parts with the same unit (1h 15m 30m
== 1h 45m
) are allowed.
Fractional values (decimal separator - dot .
) are allowed (1.5h
). Negative values are allowed (2h -20m
== 1h 40m
).
NumberFormat for a cell where this function is used can be both number and time (in last case recommended format is Cell.NumberFormat = "[h]:mm:ss" - it will not cut hours over a day, for example, 15d 5h 09m 33s
will be shown as 365:09:33
, not 5:09:33
as for "h:mm:ss" format).
Include an example that how to use the Function for example=convert_text_text_to_interval(Cell refe)
.
– Rajesh S
Mar 5 at 6:09
Your code needs this corrections,,tmp As Variant
instead ofString
andCase code should Case "h"
instead ofCase 'h'
. Also what about the Format of the Formula Cell !!
– Rajesh S
Mar 5 at 6:21
Case
code shouldCase "h"
instead ofCase 'h'
Thanks, edited.tmp As Variant
instead ofString
Edited too. what about the Format of the Formula Cell As user needed (maybe number, maybe time).
– Akina
Mar 5 at 6:28
For this line ,,dim temp() as string, tmp
it shoulddim temp() as string, tmp as Variant
. And for the first example if Formula cell is Number theAns is Zero
,, if Time the is12:07:00 AM
,, I think it should01:46:06 AM
instead.
– Rajesh S
Mar 5 at 6:41
@RajeshS And for the first example if Formula cell is Number the Ans is Zero,, if Time the is 12:07:00 AM ,, I think it should 01:46:06 AM instead. I cannot understand this phrase. And whyAM
- it is interval, not time.
– Akina
Mar 5 at 6:55
|
show 3 more comments
Public Function convert_text_to_interval(interval As String) As Double
Dim temp() As String, tmp As Variant
On Error GoTo error_handler
temp = Split(interval)
For Each tmp In temp
Select Case Right(Trim(tmp), 1)
Case "d"
convert_text_to_interval = convert_text_to_interval + Val(tmp)
Case "h"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 24#
Case "m"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 1440#
Case "s"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 86400#
End Select
Next
Exit Function
error_handler:
convert_text_to_interval = 0
End Function
The function must be inserted into a common module (not to a sheet or class module!).
After insertion it will be available in formula master in User-defined functions section. Or it can be inserted directly (for example, as =convert_text_to_interval(A1)
).
The spaces between interval parts are compulsory (1h30m
not allowed, it will be treated as 1m
, see below).
The unit letter must be adjacent to a value (1 h 30 m
not allowed, it will be treated as 0h 0m
, see below).
The parts without correct unit letter are ignored. If unit part contains more than 1 letter, the last one is used, all another are ignored (1hm
== 1m
). All non-digit symbols except the last unit letter are ignored.
Multiple parts with the same unit (1h 15m 30m
== 1h 45m
) are allowed.
Fractional values (decimal separator - dot .
) are allowed (1.5h
). Negative values are allowed (2h -20m
== 1h 40m
).
NumberFormat for a cell where this function is used can be both number and time (in last case recommended format is Cell.NumberFormat = "[h]:mm:ss" - it will not cut hours over a day, for example, 15d 5h 09m 33s
will be shown as 365:09:33
, not 5:09:33
as for "h:mm:ss" format).
Include an example that how to use the Function for example=convert_text_text_to_interval(Cell refe)
.
– Rajesh S
Mar 5 at 6:09
Your code needs this corrections,,tmp As Variant
instead ofString
andCase code should Case "h"
instead ofCase 'h'
. Also what about the Format of the Formula Cell !!
– Rajesh S
Mar 5 at 6:21
Case
code shouldCase "h"
instead ofCase 'h'
Thanks, edited.tmp As Variant
instead ofString
Edited too. what about the Format of the Formula Cell As user needed (maybe number, maybe time).
– Akina
Mar 5 at 6:28
For this line ,,dim temp() as string, tmp
it shoulddim temp() as string, tmp as Variant
. And for the first example if Formula cell is Number theAns is Zero
,, if Time the is12:07:00 AM
,, I think it should01:46:06 AM
instead.
– Rajesh S
Mar 5 at 6:41
@RajeshS And for the first example if Formula cell is Number the Ans is Zero,, if Time the is 12:07:00 AM ,, I think it should 01:46:06 AM instead. I cannot understand this phrase. And whyAM
- it is interval, not time.
– Akina
Mar 5 at 6:55
|
show 3 more comments
Public Function convert_text_to_interval(interval As String) As Double
Dim temp() As String, tmp As Variant
On Error GoTo error_handler
temp = Split(interval)
For Each tmp In temp
Select Case Right(Trim(tmp), 1)
Case "d"
convert_text_to_interval = convert_text_to_interval + Val(tmp)
Case "h"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 24#
Case "m"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 1440#
Case "s"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 86400#
End Select
Next
Exit Function
error_handler:
convert_text_to_interval = 0
End Function
The function must be inserted into a common module (not to a sheet or class module!).
After insertion it will be available in formula master in User-defined functions section. Or it can be inserted directly (for example, as =convert_text_to_interval(A1)
).
The spaces between interval parts are compulsory (1h30m
not allowed, it will be treated as 1m
, see below).
The unit letter must be adjacent to a value (1 h 30 m
not allowed, it will be treated as 0h 0m
, see below).
The parts without correct unit letter are ignored. If unit part contains more than 1 letter, the last one is used, all another are ignored (1hm
== 1m
). All non-digit symbols except the last unit letter are ignored.
Multiple parts with the same unit (1h 15m 30m
== 1h 45m
) are allowed.
Fractional values (decimal separator - dot .
) are allowed (1.5h
). Negative values are allowed (2h -20m
== 1h 40m
).
NumberFormat for a cell where this function is used can be both number and time (in last case recommended format is Cell.NumberFormat = "[h]:mm:ss" - it will not cut hours over a day, for example, 15d 5h 09m 33s
will be shown as 365:09:33
, not 5:09:33
as for "h:mm:ss" format).
Public Function convert_text_to_interval(interval As String) As Double
Dim temp() As String, tmp As Variant
On Error GoTo error_handler
temp = Split(interval)
For Each tmp In temp
Select Case Right(Trim(tmp), 1)
Case "d"
convert_text_to_interval = convert_text_to_interval + Val(tmp)
Case "h"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 24#
Case "m"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 1440#
Case "s"
convert_text_to_interval = convert_text_to_interval + Val(tmp) / 86400#
End Select
Next
Exit Function
error_handler:
convert_text_to_interval = 0
End Function
The function must be inserted into a common module (not to a sheet or class module!).
After insertion it will be available in formula master in User-defined functions section. Or it can be inserted directly (for example, as =convert_text_to_interval(A1)
).
The spaces between interval parts are compulsory (1h30m
not allowed, it will be treated as 1m
, see below).
The unit letter must be adjacent to a value (1 h 30 m
not allowed, it will be treated as 0h 0m
, see below).
The parts without correct unit letter are ignored. If unit part contains more than 1 letter, the last one is used, all another are ignored (1hm
== 1m
). All non-digit symbols except the last unit letter are ignored.
Multiple parts with the same unit (1h 15m 30m
== 1h 45m
) are allowed.
Fractional values (decimal separator - dot .
) are allowed (1.5h
). Negative values are allowed (2h -20m
== 1h 40m
).
NumberFormat for a cell where this function is used can be both number and time (in last case recommended format is Cell.NumberFormat = "[h]:mm:ss" - it will not cut hours over a day, for example, 15d 5h 09m 33s
will be shown as 365:09:33
, not 5:09:33
as for "h:mm:ss" format).
edited Mar 5 at 6:53
answered Mar 5 at 5:28
AkinaAkina
1,36929
1,36929
Include an example that how to use the Function for example=convert_text_text_to_interval(Cell refe)
.
– Rajesh S
Mar 5 at 6:09
Your code needs this corrections,,tmp As Variant
instead ofString
andCase code should Case "h"
instead ofCase 'h'
. Also what about the Format of the Formula Cell !!
– Rajesh S
Mar 5 at 6:21
Case
code shouldCase "h"
instead ofCase 'h'
Thanks, edited.tmp As Variant
instead ofString
Edited too. what about the Format of the Formula Cell As user needed (maybe number, maybe time).
– Akina
Mar 5 at 6:28
For this line ,,dim temp() as string, tmp
it shoulddim temp() as string, tmp as Variant
. And for the first example if Formula cell is Number theAns is Zero
,, if Time the is12:07:00 AM
,, I think it should01:46:06 AM
instead.
– Rajesh S
Mar 5 at 6:41
@RajeshS And for the first example if Formula cell is Number the Ans is Zero,, if Time the is 12:07:00 AM ,, I think it should 01:46:06 AM instead. I cannot understand this phrase. And whyAM
- it is interval, not time.
– Akina
Mar 5 at 6:55
|
show 3 more comments
Include an example that how to use the Function for example=convert_text_text_to_interval(Cell refe)
.
– Rajesh S
Mar 5 at 6:09
Your code needs this corrections,,tmp As Variant
instead ofString
andCase code should Case "h"
instead ofCase 'h'
. Also what about the Format of the Formula Cell !!
– Rajesh S
Mar 5 at 6:21
Case
code shouldCase "h"
instead ofCase 'h'
Thanks, edited.tmp As Variant
instead ofString
Edited too. what about the Format of the Formula Cell As user needed (maybe number, maybe time).
– Akina
Mar 5 at 6:28
For this line ,,dim temp() as string, tmp
it shoulddim temp() as string, tmp as Variant
. And for the first example if Formula cell is Number theAns is Zero
,, if Time the is12:07:00 AM
,, I think it should01:46:06 AM
instead.
– Rajesh S
Mar 5 at 6:41
@RajeshS And for the first example if Formula cell is Number the Ans is Zero,, if Time the is 12:07:00 AM ,, I think it should 01:46:06 AM instead. I cannot understand this phrase. And whyAM
- it is interval, not time.
– Akina
Mar 5 at 6:55
Include an example that how to use the Function for example
=convert_text_text_to_interval(Cell refe)
.– Rajesh S
Mar 5 at 6:09
Include an example that how to use the Function for example
=convert_text_text_to_interval(Cell refe)
.– Rajesh S
Mar 5 at 6:09
Your code needs this corrections,,
tmp As Variant
instead of String
and Case code should Case "h"
instead of Case 'h'
. Also what about the Format of the Formula Cell !!– Rajesh S
Mar 5 at 6:21
Your code needs this corrections,,
tmp As Variant
instead of String
and Case code should Case "h"
instead of Case 'h'
. Also what about the Format of the Formula Cell !!– Rajesh S
Mar 5 at 6:21
Case
code should Case "h"
instead of Case 'h'
Thanks, edited. tmp As Variant
instead of String
Edited too. what about the Format of the Formula Cell As user needed (maybe number, maybe time).– Akina
Mar 5 at 6:28
Case
code should Case "h"
instead of Case 'h'
Thanks, edited. tmp As Variant
instead of String
Edited too. what about the Format of the Formula Cell As user needed (maybe number, maybe time).– Akina
Mar 5 at 6:28
For this line ,,
dim temp() as string, tmp
it should dim temp() as string, tmp as Variant
. And for the first example if Formula cell is Number the Ans is Zero
,, if Time the is 12:07:00 AM
,, I think it should 01:46:06 AM
instead.– Rajesh S
Mar 5 at 6:41
For this line ,,
dim temp() as string, tmp
it should dim temp() as string, tmp as Variant
. And for the first example if Formula cell is Number the Ans is Zero
,, if Time the is 12:07:00 AM
,, I think it should 01:46:06 AM
instead.– Rajesh S
Mar 5 at 6:41
@RajeshS And for the first example if Formula cell is Number the Ans is Zero,, if Time the is 12:07:00 AM ,, I think it should 01:46:06 AM instead. I cannot understand this phrase. And why
AM
- it is interval, not time.– Akina
Mar 5 at 6:55
@RajeshS And for the first example if Formula cell is Number the Ans is Zero,, if Time the is 12:07:00 AM ,, I think it should 01:46:06 AM instead. I cannot understand this phrase. And why
AM
- it is interval, not time.– Akina
Mar 5 at 6:55
|
show 3 more comments
Parses a String of text for 'd' 'h' 'm' 's' values and converts to time.
This will not replace the string values. Instead, use this formula to create a helper column for the time arithmetic.
- To enable the display of over 24 hours in in the time formula cells, use the
[h]:mm:ss
time format. - All value & letter pairs in the time string are optional. If the letter is present, the value must be attached.
- No space between value and unit letter
3h
not3 h
(not: 3 space h) - Separate one pair from the next with a space.
3d 2h
(3d space 2h)
This is an error:3d2h
(if it has two digits, like the 11 in3d11h
, it should be ok). - The letters are case insensitive (e.g. 'h' or 'H').
Multiline formatted formula: Paste directly into the formula bar
to avoid splitting the formula over multiple rows.
=VALUE( IFERROR( LEFT( I11, SEARCH( "d", I11) - 1),0) * 24 +
IFERROR( IFERROR( MID( I11, SEARCH( "h", I11) - 2, 2), MID( I11, SEARCH( "h", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "m", I11) - 2, 2), MID( I11, SEARCH( "m", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "s", I11) - 2, 2), MID( I11, SEARCH( "s", I11) - 1, 1)), 0))
The value I11
is the cell with the time string (search and replace).
- Or create (insert) helper column.
- Insert new row.
- Paste formula in new row in helper column.
- Cut cell I11 and paste in new row's time string column (assumes I11 is not referenced by an existing formula).
- Copy the contents of original I11 back to the formula bar of I11 so the relative addresses are not moved back, just the contents.
It's working,, now you get 10 ☺
– Rajesh S
Mar 5 at 7:23
add a comment |
Parses a String of text for 'd' 'h' 'm' 's' values and converts to time.
This will not replace the string values. Instead, use this formula to create a helper column for the time arithmetic.
- To enable the display of over 24 hours in in the time formula cells, use the
[h]:mm:ss
time format. - All value & letter pairs in the time string are optional. If the letter is present, the value must be attached.
- No space between value and unit letter
3h
not3 h
(not: 3 space h) - Separate one pair from the next with a space.
3d 2h
(3d space 2h)
This is an error:3d2h
(if it has two digits, like the 11 in3d11h
, it should be ok). - The letters are case insensitive (e.g. 'h' or 'H').
Multiline formatted formula: Paste directly into the formula bar
to avoid splitting the formula over multiple rows.
=VALUE( IFERROR( LEFT( I11, SEARCH( "d", I11) - 1),0) * 24 +
IFERROR( IFERROR( MID( I11, SEARCH( "h", I11) - 2, 2), MID( I11, SEARCH( "h", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "m", I11) - 2, 2), MID( I11, SEARCH( "m", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "s", I11) - 2, 2), MID( I11, SEARCH( "s", I11) - 1, 1)), 0))
The value I11
is the cell with the time string (search and replace).
- Or create (insert) helper column.
- Insert new row.
- Paste formula in new row in helper column.
- Cut cell I11 and paste in new row's time string column (assumes I11 is not referenced by an existing formula).
- Copy the contents of original I11 back to the formula bar of I11 so the relative addresses are not moved back, just the contents.
It's working,, now you get 10 ☺
– Rajesh S
Mar 5 at 7:23
add a comment |
Parses a String of text for 'd' 'h' 'm' 's' values and converts to time.
This will not replace the string values. Instead, use this formula to create a helper column for the time arithmetic.
- To enable the display of over 24 hours in in the time formula cells, use the
[h]:mm:ss
time format. - All value & letter pairs in the time string are optional. If the letter is present, the value must be attached.
- No space between value and unit letter
3h
not3 h
(not: 3 space h) - Separate one pair from the next with a space.
3d 2h
(3d space 2h)
This is an error:3d2h
(if it has two digits, like the 11 in3d11h
, it should be ok). - The letters are case insensitive (e.g. 'h' or 'H').
Multiline formatted formula: Paste directly into the formula bar
to avoid splitting the formula over multiple rows.
=VALUE( IFERROR( LEFT( I11, SEARCH( "d", I11) - 1),0) * 24 +
IFERROR( IFERROR( MID( I11, SEARCH( "h", I11) - 2, 2), MID( I11, SEARCH( "h", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "m", I11) - 2, 2), MID( I11, SEARCH( "m", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "s", I11) - 2, 2), MID( I11, SEARCH( "s", I11) - 1, 1)), 0))
The value I11
is the cell with the time string (search and replace).
- Or create (insert) helper column.
- Insert new row.
- Paste formula in new row in helper column.
- Cut cell I11 and paste in new row's time string column (assumes I11 is not referenced by an existing formula).
- Copy the contents of original I11 back to the formula bar of I11 so the relative addresses are not moved back, just the contents.
Parses a String of text for 'd' 'h' 'm' 's' values and converts to time.
This will not replace the string values. Instead, use this formula to create a helper column for the time arithmetic.
- To enable the display of over 24 hours in in the time formula cells, use the
[h]:mm:ss
time format. - All value & letter pairs in the time string are optional. If the letter is present, the value must be attached.
- No space between value and unit letter
3h
not3 h
(not: 3 space h) - Separate one pair from the next with a space.
3d 2h
(3d space 2h)
This is an error:3d2h
(if it has two digits, like the 11 in3d11h
, it should be ok). - The letters are case insensitive (e.g. 'h' or 'H').
Multiline formatted formula: Paste directly into the formula bar
to avoid splitting the formula over multiple rows.
=VALUE( IFERROR( LEFT( I11, SEARCH( "d", I11) - 1),0) * 24 +
IFERROR( IFERROR( MID( I11, SEARCH( "h", I11) - 2, 2), MID( I11, SEARCH( "h", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "m", I11) - 2, 2), MID( I11, SEARCH( "m", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "s", I11) - 2, 2), MID( I11, SEARCH( "s", I11) - 1, 1)), 0))
The value I11
is the cell with the time string (search and replace).
- Or create (insert) helper column.
- Insert new row.
- Paste formula in new row in helper column.
- Cut cell I11 and paste in new row's time string column (assumes I11 is not referenced by an existing formula).
- Copy the contents of original I11 back to the formula bar of I11 so the relative addresses are not moved back, just the contents.
edited Mar 5 at 15:03
answered Mar 5 at 7:11
Ted D.Ted D.
75028
75028
It's working,, now you get 10 ☺
– Rajesh S
Mar 5 at 7:23
add a comment |
It's working,, now you get 10 ☺
– Rajesh S
Mar 5 at 7:23
It's working,, now you get 10 ☺
– Rajesh S
Mar 5 at 7:23
It's working,, now you get 10 ☺
– Rajesh S
Mar 5 at 7:23
add a comment |
Your issue can be solved by using few Helper Columns also:
How it works:
- Sample data in Range
A4:E29
.
D, H, M & S
indicatesDay, Hour, Minute &
.
Second
Formula in B4 & fill down:
=VALUE(IFERROR(LEFT(A4,SEARCH("d",A4)-1),0)*24)/24
Formula in C4 & fill down:
=IF(ISNUMBER(SEARCH(C$3,$A4)),MID($A4,IF(SEARCH(C$3,$A4)<5,1,SEARCH(C$3,$A4)-2),IF(SEARCH(C$3,$A4)<5,SEARCH(C$3,$A4)-1,2)),0)*1
Formula in D4 & fill down:
=IF(ISNUMBER(SEARCH(D$3,$A4)),MID($A4,IF(SEARCH(D$3,$A4)<5,1,SEARCH(D$3,$A4)-2),IF(SEARCH(D$3,$A4)<5,SEARCH(D$3,$A4)-1,2)),0)*1
Formula in E4 & fill down:
=IF(ISNUMBER(SEARCH(E$3,$A4)),MID($A4,IF(SEARCH(E$3,$A4)<5,1,SEARCH(E$3,$A4)-2),IF(SEARCH(E$3,$A4)<5,SEARCH(E$3,$A4)-1,2)),0)*1
Final Formula in F4:
=IF(LEN(A4)>1,B4+C4/24+D4/(24*60)+E4/(24*60^2),"")
N.B.
- Adjust cell references in the Formula as
needed. - Cell Format for Range
A4:E29
is
GENERAL
. - Cell Format for Range
F4:F29
is
[h]:mm:ss
. - You can hide Helper Columns as soon you
get the job done.
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:09
@Rox,, glad to help you,, I've tried to include as many example I can,, follow instructions properly, you also find is working. ☺
– Rajesh S
Mar 5 at 10:11
add a comment |
Your issue can be solved by using few Helper Columns also:
How it works:
- Sample data in Range
A4:E29
.
D, H, M & S
indicatesDay, Hour, Minute &
.
Second
Formula in B4 & fill down:
=VALUE(IFERROR(LEFT(A4,SEARCH("d",A4)-1),0)*24)/24
Formula in C4 & fill down:
=IF(ISNUMBER(SEARCH(C$3,$A4)),MID($A4,IF(SEARCH(C$3,$A4)<5,1,SEARCH(C$3,$A4)-2),IF(SEARCH(C$3,$A4)<5,SEARCH(C$3,$A4)-1,2)),0)*1
Formula in D4 & fill down:
=IF(ISNUMBER(SEARCH(D$3,$A4)),MID($A4,IF(SEARCH(D$3,$A4)<5,1,SEARCH(D$3,$A4)-2),IF(SEARCH(D$3,$A4)<5,SEARCH(D$3,$A4)-1,2)),0)*1
Formula in E4 & fill down:
=IF(ISNUMBER(SEARCH(E$3,$A4)),MID($A4,IF(SEARCH(E$3,$A4)<5,1,SEARCH(E$3,$A4)-2),IF(SEARCH(E$3,$A4)<5,SEARCH(E$3,$A4)-1,2)),0)*1
Final Formula in F4:
=IF(LEN(A4)>1,B4+C4/24+D4/(24*60)+E4/(24*60^2),"")
N.B.
- Adjust cell references in the Formula as
needed. - Cell Format for Range
A4:E29
is
GENERAL
. - Cell Format for Range
F4:F29
is
[h]:mm:ss
. - You can hide Helper Columns as soon you
get the job done.
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:09
@Rox,, glad to help you,, I've tried to include as many example I can,, follow instructions properly, you also find is working. ☺
– Rajesh S
Mar 5 at 10:11
add a comment |
Your issue can be solved by using few Helper Columns also:
How it works:
- Sample data in Range
A4:E29
.
D, H, M & S
indicatesDay, Hour, Minute &
.
Second
Formula in B4 & fill down:
=VALUE(IFERROR(LEFT(A4,SEARCH("d",A4)-1),0)*24)/24
Formula in C4 & fill down:
=IF(ISNUMBER(SEARCH(C$3,$A4)),MID($A4,IF(SEARCH(C$3,$A4)<5,1,SEARCH(C$3,$A4)-2),IF(SEARCH(C$3,$A4)<5,SEARCH(C$3,$A4)-1,2)),0)*1
Formula in D4 & fill down:
=IF(ISNUMBER(SEARCH(D$3,$A4)),MID($A4,IF(SEARCH(D$3,$A4)<5,1,SEARCH(D$3,$A4)-2),IF(SEARCH(D$3,$A4)<5,SEARCH(D$3,$A4)-1,2)),0)*1
Formula in E4 & fill down:
=IF(ISNUMBER(SEARCH(E$3,$A4)),MID($A4,IF(SEARCH(E$3,$A4)<5,1,SEARCH(E$3,$A4)-2),IF(SEARCH(E$3,$A4)<5,SEARCH(E$3,$A4)-1,2)),0)*1
Final Formula in F4:
=IF(LEN(A4)>1,B4+C4/24+D4/(24*60)+E4/(24*60^2),"")
N.B.
- Adjust cell references in the Formula as
needed. - Cell Format for Range
A4:E29
is
GENERAL
. - Cell Format for Range
F4:F29
is
[h]:mm:ss
. - You can hide Helper Columns as soon you
get the job done.
Your issue can be solved by using few Helper Columns also:
How it works:
- Sample data in Range
A4:E29
.
D, H, M & S
indicatesDay, Hour, Minute &
.
Second
Formula in B4 & fill down:
=VALUE(IFERROR(LEFT(A4,SEARCH("d",A4)-1),0)*24)/24
Formula in C4 & fill down:
=IF(ISNUMBER(SEARCH(C$3,$A4)),MID($A4,IF(SEARCH(C$3,$A4)<5,1,SEARCH(C$3,$A4)-2),IF(SEARCH(C$3,$A4)<5,SEARCH(C$3,$A4)-1,2)),0)*1
Formula in D4 & fill down:
=IF(ISNUMBER(SEARCH(D$3,$A4)),MID($A4,IF(SEARCH(D$3,$A4)<5,1,SEARCH(D$3,$A4)-2),IF(SEARCH(D$3,$A4)<5,SEARCH(D$3,$A4)-1,2)),0)*1
Formula in E4 & fill down:
=IF(ISNUMBER(SEARCH(E$3,$A4)),MID($A4,IF(SEARCH(E$3,$A4)<5,1,SEARCH(E$3,$A4)-2),IF(SEARCH(E$3,$A4)<5,SEARCH(E$3,$A4)-1,2)),0)*1
Final Formula in F4:
=IF(LEN(A4)>1,B4+C4/24+D4/(24*60)+E4/(24*60^2),"")
N.B.
- Adjust cell references in the Formula as
needed. - Cell Format for Range
A4:E29
is
GENERAL
. - Cell Format for Range
F4:F29
is
[h]:mm:ss
. - You can hide Helper Columns as soon you
get the job done.
edited Mar 6 at 10:23
answered Mar 5 at 9:19
Rajesh SRajesh S
4,4282724
4,4282724
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:09
@Rox,, glad to help you,, I've tried to include as many example I can,, follow instructions properly, you also find is working. ☺
– Rajesh S
Mar 5 at 10:11
add a comment |
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:09
@Rox,, glad to help you,, I've tried to include as many example I can,, follow instructions properly, you also find is working. ☺
– Rajesh S
Mar 5 at 10:11
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:09
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:09
@Rox,, glad to help you,, I've tried to include as many example I can,, follow instructions properly, you also find is working. ☺
– Rajesh S
Mar 5 at 10:11
@Rox,, glad to help you,, I've tried to include as many example I can,, follow instructions properly, you also find is working. ☺
– Rajesh S
Mar 5 at 10:11
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1411316%2fconvert-time-string-text-to-time-calculable%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
These are all really good ideas. I’ll test them out, particularly non-code ones since I need to keep the file simple for those who inherit the file later on
– Rox
Mar 5 at 10:11