Creating Heat Map - Round down to the 30 minute intervals

[DarrenF]

Hello,

Need some help trying to figure out why when I create a heat map from different timeframes that I'm only getting ":00" and ":30" and not 8:00am, 8:30am, 9:00am, 9:30am...etc. I'm using the formula =FLOOR(N2,"0:30") to round the timeframe to the half hour. I'm sure using "0:30" is probably what's causing that to happen. Does know a different way I can get the time frame to round down to the nearest 30 minute time frame.

Example:
All the times below should be 8:00am

8:15am
8:22am
8:25am
8:29am

 

[Scott Huish]

First off, Excel won't recognize that as a time without a space before the am/pm.
When that is fixed, format as a time and it should work fine.

Excel 2010
	N	O
2	8:15 AM	8:00 AM
3	8:22 AM	8:00 AM
4	8:25 AM	8:00 AM
5	8:29 AM	8:00 AM
Sheet1

Cell Formulas
Range	Formula
O2	=FLOOR(N2,"0:30")
O3	=FLOOR(N3,"0:30")
O4	=FLOOR(N4,"0:30")
O5	=FLOOR(N5,"0:30")

 

[DarrenF]

Thanks Scott for the reply, I had it formatted correctly in excel.

When I make it a pivot table my column is only showing :00 and :30, it wont show each time interval from 8:00 AM to 5:00 PM by the half hour.