Convert Text Time into Time Format

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[amitrathi239 · Accepted solution]

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")

[Former Member · Accepted solution]

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

[amitrathi239 · Accepted solution]

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")

[Former Member · Accepted solution]

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.

[amitrathi239 · Accepted solution]

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

like

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

[Former Member · Accepted solution]

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).

[Former Member · Accepted solution]

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

[amitrathi239 · Accepted solution]

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?