=or less

  • Thread starter Thread starter Carol
  • Start date Start date
C

Carol

Hi
need help please.
I need to know how to wright a formula that will say if a number is
11 or less than another number.

cell a1 has 734 and cell a2 has 735 ( these numbers will change at
times) but I need to know how to tell if the lower cell a2 is 11 or
less than the cell up from it a1. Does this make sense?

This don't work for me =if(a2<=11a1,"x")

Thanks
Carol
 
The formula: =IF(ABS(A2-A1)<=11,"Yes","")

will display the message "Yes" when difference between the two numbers
is less than or equal to 11.
 
Thanks alot, but I ran into 1 more problem.
dealing in time 12:55 a1 and 1:22 a2. I get 1:22 is less than 12:55.
Excel see's the time in numbers not time, I have the cells formatted
in time. Any simple way around this?

Thanks for your help...

Carol
 
Make sure you enter AM or PM for each time????


Thanks alot, but I ran into 1 more problem.
dealing in time 12:55 a1 and 1:22 a2. I get 1:22 is less than 12:55.
Excel see's the time in numbers not time, I have the cells formatted
in time. Any simple way around this?

Thanks for your help...

Carol
Click to expand...
 
Thats one of my problems, I have the cell formatted as #":"00 so when
I type in a time it will come out as a time format. typing am or pm
will mean another 220 hits on the keyboard each time I use the sheet
(many times a day)
Excel is great, but sometimes solving 1 problem leads to another.

Thanks Carol
 
Use a 24 hour clock, and you can subtract hours within the same day,
without changing your formatting.
 
Thanks for your help, 24hr clock won't work for me, looks like I'm in
a circle with problems with this sheet. I'm giving up.

Carol
 
For a time format use hh:mm (a standard format available from the
Format | Cells... | Number tab dialog box). There's no reason for a
custom #":"00

If your AM and PM times are guaranteed to *not* overlap, you could use
a programmatic solution...

Put the following in your worksheet's code module. It assumes that any
time between 00:00 and 07:00 is a PM time and any other time is a AM
time.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
For Each aCell In Target
If IsDate(aCell.Text) And Not aCell.HasFormula Then
If aCell.Value < #7:00:00 AM# Then
On Error Resume Next
Application.EnableEvents = False
aCell.Value = aCell.Value + #12:00:00 PM#
Application.EnableEvents = True
On Error GoTo 0
End If
End If
Next aCell
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Thanks, but that sub worksheet is way above me, and if I use the time
format I have I have to type (shift / Colon) on every time. Doing this
2 to 300 times a day is what I want to get out of.

Thanks
Carol
 
Did you take a look at Chip Pearson's site that helps with time/date entry (from
your previous thread)?

If you aren't Bobby <vbg>, take a look here:
http://www.cpearson.com/excel/DateTimeEntry.htm

You put that code behind the worksheet that should have this behavior and change
the range (from A1:A10 to what you want) and forget about it.

If you try it, you may find that you like it.
 
Nope, I'm Carol, but working with Bob, and I quit working on this, I
know I'll just run into more problems and I don't have the time,
besides we gave what we had to a co-worker.

Thanks
Carol
 
122 is less than 1255. You have your cells formatted to look like time, but
your using numbers and not time values.

If you enter 700 is it 7:00 am or 7:00 PM?
 
Back
Top