Hi to those that have assisted me so far. I have spent some time digesting the advice given and following accordingly. The good thing is that as a 1st time user I’m learning quite quickly thxs to this valuable forum. So I will outline what i have done to try to resolve this issue even though its not quite there at this point. Hopefully you will see that I have laid out my info as best possible… Here goes…
Please refer to execl attachment… It will make more sense as the formatting on this forum update may confuse a little…
No Of seconds ( Report Variable )
=DaysBetween([Incident Last Resolved Date Time];[Incident Reported Date Time]) * 86400 + ( ToNumber(FormatDate([Incident Reported Date];“HH”)) * 3600 + ToNumber(Left(FormatDate([Incident Reported Date Time]; “mm:ss”) ;2)) * 60 + ToNumber( FormatDate([Incident Reported Date] ;“ss”))) - (ToNumber(FormatDate([Incident Last Resolved Date Time] ;“HH”)) * 3600 + ToNumber(Left(FormatDate([Incident Last Resolved Date Time] ;“mm:ss”) ;2)) * 60 + ToNumber(FormatDate([Incident Last Resolved Date Time] ;“ss”)))
Elapsed time in hh:mm:ss ( Formatted Column )
=FormatNumber(Floor([Number of seconds]/86400) ;“0”) + " day(s) " + FormatNumber(Floor(Mod([Number of seconds] ;86400)/3600) ;“00”) + “:” + FormatNumber(Floor(Mod(Mod([Number of seconds] ;86400) ;3600)/60) ;“00”) + “:” + FormatNumber(Mod(Mod(Mod([Number of seconds] ;86400) ;3600) ;60) ;“00”)
Contents of Query ( Without formatting report columns )
Incident Reported Date Incident Last Resolved - Date
( mm/dd/yyyy ) ( mm/dd/yyyy )
INC000002493598 06/16/2010 06/16/2010
INC000002495791 06/16/2010 06/16/2010
INC000002492887 06.15/2010 06/16/2010
Incident Reported Date Time Incident Last Resolved Date Time
( dd/mm/yy ) ( dd/mm/yy )
INC000002493598 16/06/10 16/06/10
INC000002495791 16/06/10 16/06/10
INC000002492887 15/06/10 16/06/10
Report Variable ( No of seconds ) Report Variable formatted
Number of seconds Elapsed Time
INC000002493598 -25732 -1 day(s) -08:-09:-52
INC000002495791 -48850 -1 day(s) -14:-35:-10
INC000002492887 -83785 -1 day(s) -24:-17:-25
Formats after Report is saved to excel
Incident Reported Date mm/dd/yyyy
Incident Reported Date Time dd/mm/yyyy ( Displays cell as dd/mm/yyyy hh:mm:ss AM )
Incident Last Resolved - Date dd/mm/yyyy ( Displays cell as dd/mm/yyyy 10:00:00 AM
Incident Last Resolved Date Time dd/mm/yyyy ( Displays cell as dd/mm/yyyy hh:mm:ss PM )
Elapsed time calculation in Excel
Incident No: Incident Last Resolved Date Time Incident Reported Date Time Elapsed Time
INC000002493598 16/06/2010 8:04:52 AM 16/06/2010 7:56:41 AM 0:08:11
INC000002495791 16/06/2010 1:59:10 PM 16/06/2010 1:25:23 PM 0:33:47
INC000002492887 16/06/2010 12:06:25 AM 15/06/2010 11:50:51 PM 0.15.34
So I would appreciate if someone could help me put this to bed… Maybe my
formula is wrong or that I still need to add extra smarts to get a result in hh::mm::ss
so that I can apply a filter on that value to determine if its less than or greater than
a time value… eg such as <= 4 hrs…
I appreciate your time ( believe me , im trying as well )… Cheers
Simple_Test_for_Date_Time_difference.xls (13.0 KB)
Lou (BOB member since 2010-06-08)