Change column format

A

az-willie

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.
 
D

David McRitchie

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
 
A

az-willie

David said:
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
Click to expand...
==============
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.
 
D

David McRitchie

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

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

az-willie

David said:
Try =D2<TIME(12,0,0)
=IF(D2<TIME(12,0,0),"",D2)
=IF(D2<TIME(12,0,9),D2,"")
Click to expand...
================
I'm dumb today ... well I'm admitting to today :)
I don't understand this. Hmmmm.
 
D

David McRitchie

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
 
A

az-willie

David said:
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
Click to expand...
===================
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?
 
Top