Time format and various users

[Guest]

I may be going at this the wrong way; However, I have a spreadsheet that has
two columns labeled "Time" and "AM/PM". I need the AM/PM for which Access
uses to sort report from. ( I import excel into Access)
The problem is various users input into excel and don't always place the
AM/PM into appropriate cell which causes errors upon import.
I would like to create macro which will fill in am/pm if there is a time
listed in the
"Time" column.
Any help would be appreciated.

 

[Bob Phillips]

Use a formula

-IF(A2<.5,"AM","PM")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

for each cell in Range("B1",Range("B1").End(xldown))
if isdate(cell) then
if hour(cell) < 12 then
cell.offset(0,1).Value = "AM"
else
cell.offset(0,1).Value = "PM"
end if
end if
Next

However, if people put in 10:00 in the time cell and PM in the AM/PM cell,
then the code would replace it with AM. If you are working on an 8 to 5
type schedule and the times are all between 8:00 and 5:00 rather than
military times, then


for each cell in Range("B1",Range("B1").End(xldown))
if isdate(cell) then
if hour(cell) <= 12 then
cell.offset(0,1).Value = "PM"
else
cell.offset(0,1).Value = "AM"
end if
end if
Next