Convert V-String to Time

[alfredm]

Hi everybody, I am VERY new to using Alteryx Designer. Below is information pertaining time worked by employees. This is currently in V-String format. I tried the DateTime tool to convert the V-String to Time but without success. Grateful for guidance please. I need to be able to add an subtract information by rows and columns. 

Regards

Alfred

Employee	Date	Normal	Overtime	Callout actual
1	01/03 to 31/03	152:08	143:38	08:29
2	01/03 to 31/03	160:13	102:50	38:56
3	01/03 to 31/03	180:17	99:16	21:37
4	01/03 to 31/03	178:45	109:01	08:48
5	01/03 to 31/03	189:34	76:16	29:36
6	01/03 to 31/03	144:00	87:05	54:38
7	01/03 to 31/03	179:54	103:13	00:00
8	01/03 to 31/03	165:34	111:47	00:00

[Maskell_Rascal]

Hi @alfredm 

Your explanation of the problem does not provide enough information to understand the sample data you provided. Can you provide more information on which field you are wanting converted, and maybe an example of the output you are expecting. 

Cheers!

Phil

[Luke_C]

Hi @alfredm 

To add on to @Maskell_Rascal comments, is time even the appropriate thing to convert this to? It looks like work timesheet info, perhaps it's more important to convert to a numeric value to do your calculations. For example 102:15 would be 102.25 

[apathetichell]

You have a few problems here:

1) Date is a range in your case - not a date. You need to divide this up into two columns and add relevant years. Then you can create a start and an end date and use two date/time tools (or a formal tool with datetimeparse)

2) for normal/overtime/calloutactual -  I do not believe you can create new time fields in excess of 24 hours (because these do not confirm to 24 hour times)... you can add and subtract amounts using datetimeadd or get values using datetimediff, but I do not believe you can create a value of 152 hours and 8 minutes as a distinct time field. You could store this in minutes and add/subtract it to dates using datetimeadd. Likewise, if this is a derived value you could derive it as the difference in minutes between two date fields - but again you'd derive as one distinct time type (i.e. minutes/seconds).

[alfredm]

Many thanks for taking the time to reply. This relates to employee timesheet information. With reference to the extract below, I want to be able to add together an employee's  Normal time (column 3), Overtime (column 4) and Callout time (column 5) within the date range 1 March to 31 March (column 2) to arrive at Total at Work (column 6). Currently these are String fields I'm not able to add together. I trust this clarifies my request. 

Regards

Alfred 

1	2	3	4	5	6
Employee	Date range	Normal	Overtime	Callout actual	Total at Work
1	01/03 to 31/03	152:08	143:38	08:29	304:15
2	01/03 to 31/03	160:13	102:50	38:56	301:59
3	01/03 to 31/03	180:17	99:16	21:37	301:10

[apathetichell · Accepted solution]

see the attached. I think it will either get you where you want to go - or put you in the right direction.... note this formula:

(60*tonumber(regex_replace([_currentfield_],"(\d+)\:.*","$1")))+tonumber(regex_replace([_currentfield_],".*\:(\d+)","$1")) in a multi-field formula cuts out a bunch of the steps to convert to minutes. It would usually use a formula tool and go through a multi-step process but with multiple the string fields you needed to convert this worked faster for me...

updated to show you how to use summarize tool.

[alfredm]

Thank you very much! This is helpful. 

Regards

Alfred