How to parse Order Data / Time into something meaningful

[TheBigCountry]

I have an excel file with a column that has order date and time in the same cell.

Example:
ORDER_DATE_TIME
8/3/2015 9:27
8/3/2015 11:03
8/3/2015 11:09
8/3/2015 12:23
8/3/2015 15:45
8/3/2015 10:06
8/3/2015 10:08
8/3/2015 10:09
8/3/2015 10:11
8/3/2015 10:12
8/3/2015 13:13
8/3/2015 15:04
8/3/2015 15:14
8/4/2015 9:11
8/4/2015 9:24
8/4/2015 10:17
8/4/2015 12:57
8/4/2015 13:08
8/4/2015 13:14
8/4/2015 13:18
8/4/2015 14:24
8/4/2015 14:28
8/4/2015 14:27


I have this data for the past six months.

I want to create a graph that shows the amount of orders based on the day of the week and then based the hour (ie 8:00-9:00, 9:00-10:00, 10:00-11:00, etc)

Is there a way to make excel figure out that 8/3/2015 was a Monday and then categorize the times as well?

Thanks in advance!

 

[Chihiro]

Yes.
Use 2 helper columns.

In one of them: Assuming the data in Col A
=TEXT(A2,"ddd")
This will give weekday in format (Mon, Tue...)

In second:
=TIME(HOUR(A2),0,0)
This will give time rolled up to each hour.

 

[TheBigCountry]

Thank you Chihiro! Last question - is there a way for Time to be in European 24 hour versus American AM/PM?

 

[Chihiro]

You can format the cell that has the formula.
You can set format in formula but that would force it to be in text format and not date/time value.

 

[Easwar]

The Rational Team concert (ALM) returns modified date in this format : 2016-02-08T23:24:06.166Z when i use the internal value for exporting to csv I need to convert this into HH:MM:SS for calculation. I tried INT, Value, nothing helped. Appreciate if you could help me. Thank you in advance

 

[Chihiro]

Can you upload a sample file? With just the date/time portion.

 

[Easwar]

I have attached a sample file . I tried using =LEFT(RIGHT(E2,13),8) and could get the value. But not sure if that is a correct approach. Appreciate your time. Thank you

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Easwar]

Thank you so much for the timely help. It worked !!