Question Re: Convert Text Time into Time Format in Technology Q&A

Convert Text Time into Time Format

Former Member — Tue, 23 Aug 2016 09:43:49 GMT

Start Time (Text)	Required
00:08:05	8:05 AM
00:08:12	8:12 AM
00:08:17	8:17 AM

Re: Convert Text Time into Time Format

Former Member — Wed, 24 Aug 2016 09:36:18 GMT

Use this:

=ToDate("20160101"+Right([Start Time];5);"yyyyMMddHH:mm")

Copy and paste or type it exactly - it will give you a date/time field

Then right-click on the cell that you are using it in and format the number to fit the date/time format that you require above.

Re: Convert Text Time into Time Format

Former Member — Wed, 24 Aug 2016 10:37:09 GMT

Thanks for your Support.

Could you please help me in another issue i am facing

Code	Date	Start_Time
F31901	8/15/2016	9:04:00 AM
F31901	8/15/2016	9:32:00 AM
F31902	8/15/2016	10:40:00 AM
F31902	8/15/2016	11:10:00 AM
F31902	8/15/2016	11:19:00 AM
F31903	8/15/2016	11:41:00 AM
F31903	8/15/2016	12:04:00 PM
F31903	8/15/2016	12:17:00 PM
F31903	8/15/2016	12:24:00 PM
F31903	8/15/2016	12:30:00 PM

Required Result

Code	Date	Min Time	Max Time
F31901	8/15/2016	9:04:00 AM	9:32:00 AM
F31902	8/15/2016	10:40:00 AM	11:19:00 AM
F31903	8/15/2016	11:41:00 AM	12:30:00 PM

Re: Convert Text Time into Time Format

amitrathi239 — Wed, 24 Aug 2016 13:42:16 GMT

try with these steps.

Min Time=FormatDate(Min([Start_Time]) In ([Code]);"HH:mm:ss a")

Max Time=FormatDate(Max([Start_Time]) In ([Code]);"HH:mm:ss a")

Make sure your Start_Time object datatype is Date.if Start_time is string data type then first convert in the date data type.

use these if data type is string.

Min Time=FormatDate(Min(ToDate([Start_Time];"HH:mm:ss a")) In ([Code]);"HH:mm:ss a")

MaxTime=FormatDate(Min(ToDate([Start_Time];"HH:mm:ss a")) In ([Code]);"HH:mm:ss a")

Re: Convert Text Time into Time Format

Former Member — Wed, 24 Aug 2016 15:37:26 GMT

Thanks Man. you are great.

Now i want average of time but due to data type as string i cant take average.

L01 ASM Long Name	Min	Max
460 : MUHAMMAD RIZWAN SHAHID	09:49:00	18:19:00 PM
460 : MUHAMMAD RIZWAN SHAHID	08:39:00	17:46:00 PM
460 : MUHAMMAD RIZWAN SHAHID	08:27:00	20:07:00 PM
460 : MUHAMMAD RIZWAN SHAHID	08:48:00	18:52:00 PM

Result Required

L01 ASM Long Name	Avg of Min	Avg of Max
460 : MUHAMMAD RIZWAN SHAHID	9:20:14 AM	6:02:28 PM

Re: Convert Text Time into Time Format

amitrathi239 — Wed, 24 Aug 2016 20:25:11 GMT

try with these steps.

First you need to convert string time to date and then in secs.

Secs=Average(ToNumber(FormatDate(ToDate([Max] ;"HH:mm:ss a");"HH")) * 3600 +

ToNumber(FormatDate(ToDate([Max] ;"HH:mm:ss a");"mm")) * 60 +

ToNumber(FormatDate(ToDate([Max] ;"HH:mm:ss a");"ss"))

)

with above Secs variable you will get the average in seconds.

Next is convert the seconds in hh:mm:ss.

=FormatDate(ToDate(Concatenation(Concatenation(Concatenation(Concatenation(FormatNumber(Floor([Secs]/3600);"00"); ":" ); FormatNumber(Floor(Mod([Secs];3600)/60) ;"00")); ":" ); FormatNumber(Mod(Mod([Secs];3600) ;60) ;"00"));"HH:mm:ss a");"hh:mm:ss a")

Re: Convert Text Time into Time Format

Former Member — Thu, 25 Aug 2016 09:10:17 GMT

Dear Amit,

Thanks for you support.

I have applied your given formulas and get correct result. but there is an issue with formula . creating "#ERROR" because there are some blanks.

Re: Convert Text Time into Time Format

amitrathi239 — Thu, 25 Aug 2016 09:30:21 GMT

before calculating the average replace null values with 00:00:00 and try.

=if(isnull([Min_Time])) thne "00:00:00 AM" else [Min_Time]

Re: Convert Text Time into Time Format

Former Member — Thu, 25 Aug 2016 09:41:31 GMT

Keep it simple. Use these for your Start and End time

=Max([Start Time]) in ([Code])

=Min([Start Time]) in ([Code])

Then format them how you want (as advised in the answer of mine that you marked as correct earlier) with right click, format (cell or number, depending which Webi type you are using).

Re: Convert Text Time into Time Format

Former Member — Fri, 26 Aug 2016 19:31:21 GMT

Hi Amit,

Again I need your expertise.

SM	PSO	Code	Date	Start_Time	End_Time	TIO
ARSHAD AMIR	7L1015	200749	7/1/2016	4:35 PM	8:37 PM	4:02
ARSHAD AMIR	7L1015	200749	7/1/2016	6:10 PM	10:10 PM	4:00
ARSHAD AMIR	7L1015	200761	7/1/2016	10:24 AM	2:45 PM	4:21
ARSHAD AMIR	7L1015	200762	7/2/2016	3:58 PM	7:30 PM	3:32
ARSHAD AMIR	7L1015	201667	7/2/2016	3:55 PM	6:57 PM	3:02
ARSHAD AMIR	7L1015	202261	7/2/2016	4:27 PM	5:30 PM	1:03
SHOAIB KHALIQ	8L1015	300749	7/1/2016	5:35 PM	8:37 PM	3:02
SHOAIB KHALIQ	8L1016	300750	7/1/2016	5:10 PM	10:10 PM	5:00
SHOAIB KHALIQ	8L1017	300751	7/1/2016	9:24 AM	2:45 PM	5:21
SHOAIB KHALIQ	8L1018	300752	7/2/2016	4:58 PM	7:30 PM	2:32
SHOAIB KHALIQ	8L1019	300753	7/2/2016	4:55 PM	6:57 PM	2:02
SHOAIB KHALIQ	8L1020	300754	7/2/2016	3:27 PM	5:30 PM	2:03

Result Required.

SM	Avg TIO
ARSHAD AMIR
SHOAIB KHALIQ

I need Average at "SM" level "Average of (Sum(TIO) by PSO & Date) "Firstly Sum "TIO" by PSO & Date Then Average.

Please guide

Re: Convert Text Time into Time Format

amitrathi239 — Mon, 29 Aug 2016 09:58:26 GMT

Plz start a new discussion as this is already closed.Instead of putting multiple questions in single always start a new discussion...

If you will drag SM and TIO in the table are you not getting the desired results?