I have a column with times in the format 8:20am.

I tried to change the format for the column to military time ( which is
shows in the choices )but nothing happens.

I've tried changing individual cells and that didn't work either.

How do you make it change the format? I checked and the sheet isn't
protected nor is the column in question.

What do you mean by "Military Time" (just kidding)
hhmm"R"
hhmm"Z"
hh:mm

Do a global change of "am" to " AM" and "pm" to " PM"

using Ctrl+H that should change them to the format
you set, if not pick an
empty cell and copy it then select cells to convert and
use Edit, Paste Special, Add

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"az-willie" <(E-Mail Removed)> wrote ...
> I have a column with times in the format 8:20am.
>
> I tried to change the format for the column to military time ( which is
> shows in the choices )but nothing happens.
>
> I've tried changing individual cells and that didn't work either.
>
> How do you make it change the format? I checked and the sheet isn't
> protected nor is the column in question.

David McRitchie wrote:
> What do you mean by "Military Time" (just kidding)
> hhmm"R"
> hhmm"Z"
> hh:mm
>
> Do a global change of "am" to " AM" and "pm" to " PM"
>
> using Ctrl+H that should change them to the format
> you set, if not pick an
> empty cell and copy it then select cells to convert and
> use Edit, Paste Special, Add
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "az-willie" <(E-Mail Removed)> wrote ...
>> I have a column with times in the format 8:20am.
>>
>> I tried to change the format for the column to military time ( which is
>> shows in the choices )but nothing happens.
>>
>> I've tried changing individual cells and that didn't work either.
>>
>> How do you make it change the format? I checked and the sheet isn't
>> protected nor is the column in question.
>
>
==============
Thanks, that worked.

Problem is, I want to create a formula that looks at the time in column
D and if it is less than 12:00 insert the number in column E.

I used the formula IF D2<12:00 and it says it is invalid. It won't
accept 12:00. Grrr.

Is there anyway to do this?

I think I forgot to mention this is in Excel 2007.

> I used the formula IF D2<12:00 and it says it is invalid. It won't
> accept 12:00. Grrr.

Try =D2<TIME(12,0,0)
=IF(D2<TIME(12,0,0),"",D2)
=IF(D2<TIME(12,0,9),D2,"")

David McRitchie wrote:
>> I used the formula IF D2<12:00 and it says it is invalid. It won't
>> accept 12:00. Grrr.
>
> Try =D2<TIME(12,0,0)
> =IF(D2<TIME(12,0,0),"",D2)
> =IF(D2<TIME(12,0,9),D2,"")
>
>
================
I'm dumb today ... well I'm admitting to today
I don't understand this. Hmmmm.

Are you working in the worksheet or in a macro.

They are worksheet functions
the first one is strictly logical it returns True or False
the other two would be probably in adjacent columns
one would repeat the time if noon or thereafter, and the
other would repeat the time if before noon
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"az-willie" <(E-Mail Removed)> wrote in message news:Jv7Ah.18392$(E-Mail Removed)...
> David McRitchie wrote:
> >> I used the formula IF D2<12:00 and it says it is invalid. It won't
> >> accept 12:00. Grrr.
> >
> > Try =D2<TIME(12,0,0)
> > =IF(D2<TIME(12,0,0),"",D2)
> > =IF(D2<TIME(12,0,9),D2,"")
> >
> >
> ================
> I'm dumb today ... well I'm admitting to today
> I don't understand this. Hmmmm.

David McRitchie wrote:
> Are you working in the worksheet or in a macro.
>
> They are worksheet functions
> the first one is strictly logical it returns True or False
> the other two would be probably in adjacent columns
> one would repeat the time if noon or thereafter, and the
> other would repeat the time if before noon
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "az-willie" <(E-Mail Removed)> wrote in message news:Jv7Ah.18392$(E-Mail Removed)...
>> David McRitchie wrote:
>>>> I used the formula IF D2<12:00 and it says it is invalid. It won't
>>>> accept 12:00. Grrr.
>>> Try =D2<TIME(12,0,0)
>>> =IF(D2<TIME(12,0,0),"",D2)
>>> =IF(D2<TIME(12,0,9),D2,"")
>>>
>>>
>> ================
>> I'm dumb today ... well I'm admitting to today
>> I don't understand this. Hmmmm.
>
>
===================
I'm beginning to understand ... I think

What I'm trying to do is take reading from my glucose meter ( diabetes )
that I enter into the spreadsheet and graph them according to time of day.

Basically I would like to have morning, mid-day, and evening readings
grouped together.

I don't understand the 9 in the third forumula -- why you use 9.

I wanted to have a column for each of the 3 time periods and a formula
that would check the time in column C and, depending on the time of day,
either enter the reading in column D or a zero or nothing ( as your
formula does ).

Then average each column and graph the average. Many difference graphs
could be developed form the numbers as they are subtotaled by day and
week etc.

I converted standard time to military to avoid duplicate numbers such as
10:25.

I believe I see how to get the reading when the time is after a certain
time, but how would I get the result when the time is between say 7:00
and 12:00 and between 12:01 and 6:00 between 6:01 and 11:59?