Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing AM time to PM time
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing AM time to PM time
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. -----Original Message----- 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. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing AM time to PM time
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. -- Regards, Tom Ogilvy "Richard A." wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculations for Scheduled Time vs. Actual Time Worked | Excel Discussion (Misc queries) | |||
straight time, time and a half, and double time | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
Changing decimal time into 24 hour time and reverse | Excel Discussion (Misc queries) | |||
Changing many references at a time. | Excel Discussion (Misc queries) |