Excel Time

[e2the1stpower]

I have an excel document set up to figure out how long someone has been on a computer at my job but it is not as user friendly as I would like it to be. I am trying to figure out how to get it so that when someone types in a time like 6:00 it doesn't automatically assume that it is 6:00 am. I know I can type 6:00 p and it will set it to pm but the people at my job aren't willing to adapt to that. So my question is how can I get excel to realize that the time is pm and not am with out having to type it in military time. I have the =NOW() command and was thinking that maybe I could have it read from that some how but then again I don't know much about excel and what is possible. At work we have excel 2002 if that makes a difference. Thanks in advanced for any help.

 

[AnotherHiggins]

->the people at my job aren't willing to adapt to that

It is unreasonable for folks to expect Excel to know what they're thinking. It is a computer program and can do amazing things when TOLD what to do. But it can't read minds.

So how about typing in 18:00?

But if you really want to go forward with this, I think you're going to run into a problem in that Excel only recalculates when prompted. That prompting could be someone pressing [F9] in Excel or making a change elsewhere in the spreadsheet.

Also keep in mind that any formula you use will be in a different cell. That means that if a user types in "6:00" in A1, for example, you can have "6:00 PM" show up in B1, but "6:00" will still be visible to the user in A1.*

All of that having been said, there are a few things you can try...

1) If you're open less than 12 hours - let's say from 10 AM to 9 PM, then you could use a formula to assume anything less than "10:00" is PM. If the user will put the time in A1, then this formula** could go in any other cell:
[tab][COLOR=blue white]=if(A1<timevalue("10:00"), A1 + 0.5, A1)[/color]

But that approach won't work if you are open from 8 AM to 10 PM, because how will Excel know whether "9:00" refers to AM or PM?

2) In that case, you can try your approach and compare to "=Now()". As you've probably noticed, NOW is a volatile function, meaning that it refreshes every time the sheet is changed.

Instead of having the NOW function in its own cell, I'll just put it in a larger function. Again, let's say that the user is putting a time in A1. You can put this formula in any other cell. The following formula will determine whether the current time is AM or PM** and make the time entered the same:
[tab][COLOR=blue white]=if(mod(Now(), Today())<0.5, A1, A1 + 0.5)[/color]

Good luck!


* That can be avoided, but it would require a macro (VBA code). If you're unfamiliar with Excel, it might be best to stick to formulas for now.

** For more information about how Excel deals with dates and times, including why I used "0.5" to add 12 hours, see faq68-5827

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[e2the1stpower]

Thanks for the help. It seems to me that in order to use one of those commands I would need to use two cells just to get PM. If possible I just want someone to have to type in 1:00 and get 1:00pm in a single cell. We are open from 9:30am - 9:00pm most days. As you can tell I am not the most knowledgeable person when it comes to excel so if it isn't possible without VB code then let me know and I would be more then willing to try using some VB code.

 

[Gavona]

Presumably you are entering both start time and end time in different cells and then in a third subtracting Start from End?
In that case in the third cell you could simply add 12 hours if the result is negative.
=IF(Start>End,End-start+0.5,End-Start)


Gavin