Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incorrect Am and Pm
I have two columns say A and B. In these two columns I enter the time in and
time out. Everything works fine till I reach 12:00 PM. Then for some reason excel forgets whether it is Am or Pm. Is there some way to get it to not switch back to Am? Second question: I also subtract the two columns to get a total time spent for each activity. If I have the start time entered in column A but no end time in column B I recieve an error message which throws off my running totals. Can I fix it so that it will not run the total until I have entered a time in column B? Thanks in advance, Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incorrect Am and Pm
Hi!
Take this simple test: 1:00 Is that 1:00 AM or 1:00 PM? Can't tell? Well, how is excel supposed to know? 13:00 I'll bet you can tell that is 1:00 PM If you enter the time without the AM or PM on a 12hr clock it will default to AM. For your formula problem: Hard to make a suggestion without knowing what the actual formula is. Maybe something like this: =IF(B1="","",your_formula)) Biff "MrBlackForest" wrote in message ... I have two columns say A and B. In these two columns I enter the time in and time out. Everything works fine till I reach 12:00 PM. Then for some reason excel forgets whether it is Am or Pm. Is there some way to get it to not switch back to Am? Second question: I also subtract the two columns to get a total time spent for each activity. If I have the start time entered in column A but no end time in column B I recieve an error message which throws off my running totals. Can I fix it so that it will not run the total until I have entered a time in column B? Thanks in advance, Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incorrect Am and Pm
Hi Black,
Subtracting times before midnight from a time after midnight is done by adding 24 hours first. Since time is a fraction of a day the addition of 1 day is what is added. = endtime - starttime + (endtime < starttime) = C2 - B2 + (C2<B2) More information on Date and Time http://www.mvps.org/dmcritchie/excel/datetime.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "MrBlackForest" wrote in message ... I have two columns say A and B. In these two columns I enter the time in and time out. Everything works fine till I reach 12:00 PM. Then for some reason excel forgets whether it is Am or Pm. Is there some way to get it to not switch back to Am? Second question: I also subtract the two columns to get a total time spent for each activity. If I have the start time entered in column A but no end time in column B I recieve an error message which throws off my running totals. Can I fix it so that it will not run the total until I have entered a time in column B? Thanks in advance, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
incorrect calculation | Excel Discussion (Misc queries) | |||
"IF" formula returns zero or other incorrect number | Excel Worksheet Functions | |||
Formulas in the spreadsheet I use as a model are now incorrect. | Excel Discussion (Misc queries) | |||
Polynomial Formula Incorrect? | Charts and Charting in Excel | |||
After installing SP1 for XL2003 Subtotals show incorrect result | Excel Discussion (Misc queries) |