Time stamp to Hours is not extracting properly

[shaan007]

Hi All,

I am trying to extract the 24 hour time from a timestamp Date Time

example ;

,Time(round(frac([TransCont DateTime]),1/24), 'hh') as [TransCount 24 Hours]

but the the table is showing

TransCont DateTime	TransCount 24 Hours
1/04/2011 10:23 AM	10
1/04/2011 10:24 AM	10
1/04/2011 10:25 AM	10
1/04/2011 10:26 AM	10
1/04/2011 10:27 AM	10
1/04/2011 10:28 AM	10
1/04/2011 10:29 AM	10
1/04/2011 10:30 AM	11
1/04/2011 10:31 AM	11
1/04/2011 10:32 AM	11
1/04/2011 10:33 AM	11
1/04/2011 10:34 AM	11
1/04/2011 10:35 AM	11
1/04/2011 10:36 AM	11

I noticed its converting 9.30-10.30 as 10  and  10.30-11.30 as 11.

Please anyone  got any idea? how can i create a correct table

[rwunderlich · Accepted solution]

That's because you are using round(), and those appear to be correct rounding. If you just want the hour, use:

hour([TransCont DateTime])

-Rob

[jagan]

Hi Prasanna,

Below script working for me, please check

Hour(TimeStamp#(TimeStam, 'M/D/YYYY hh:mm tt' )) AS Hour

Regards,

Jagan.

[shaan007]

Thank you Rob,

It worked.

[pratap6699]

1. Name the Second sheet as ‘Dashboard’ and display the following information –

• Productive and Nonproductive Hrs. for each week
• Number of Employees present each day of the month per supervisor
• Avg. Time Spent by Employee on Floor
• Calculate Team Utilization (use formula -Total time/Number of days per activity )

anybody solve this please