Changing AM time to PM time

[Richard A.]

I have a huge problem with my Excel spreadsheet and cannot
find an answer anywhere. Here is my situation.

I have a master spreadsheet that is populated from another
spreadhseet that is created and exported by an Access
database. The information in the Access database contains
information entered in by people on a web page that
requires them to enter a time sheet for each work week.

However, here lies my problem. On the web page, there are
fields specifically named AM Start, AM Stop, PM Start and
PM Stop. All a person has to do is enter 7:00 in the AM
Start field and 11:30 for the AM Stop field. The same
holds true when a person enters 12:30 for the PM Start and
5:00 for the PM Stop. As you can tell, I am having a time
problem when this information is exported to my first
spreadsheet that is linked to my master spreadsheet.

The master spreadsheet calculates the AM times correctly
(all I want to do is to find out how much is worked
between start and stop). But with the PM times, it
interprets the times entered as AM! For example, when a
person enters 1:00 for the PM Start time, Excel interprets
this as 1:00 AM! Doing research, the only way around this
is if people enter military time which is crazy!

How do I work around this problem? Are there any formulas
or some format I can apply to the cells that contain PM
Start and Stop times that will correctly compute my
times? Please help !!

Thanks a million.

Richard A.

 

[Pete McCosh]

Richard,

one way would be to add 0.5 to your PM values. Depending
on how you import the values you could do it during that
process, or just have another column after the raw data
wit that formula in it.

Pete.

 

[Tom Ogilvy]

If you are only interested in the elapsed time, what difference does it make
if you are finding the difference between times between midnight and noon
and then again between noon and midnight. If the latter times are
interpreted as being before noon, it wouldn't change the elapsed time.
But, as advised, time is stored as a fraction of a day, so if add 0.5 to
your times, it should put thim back between noon and midnight.