Help with IF Function for times.
 

Hello,

I hope you are all well and this is in the correct section.


I have a problem with an IF command.

-------------------------------------------------------------------------------
Details;

-EXCEL 2010

-All cells containing times are custom formatted to show hh:mm

A1 - IF formula.
B1 - Contains a formula that displays an average time for a range of cells.
C1 - Contains [=now] for current time.

-----------------------------------------------------------------------------------

Basically I would like A1 to check if the current time (c1) is later than B1.
If the argument is true (if C1 is later than B1) it should return Available?
If it is false (not later) then it should return un-available.


Thanks in advance,
Ryan.

Help with IF Function for times.
 

Hi,

Am Thu, 21 Feb 2013 05:02:12 +0000 schrieb Ryanoo:

Basically I would like A1 to check if the current time (c1) is later
than B1.
If the argument is true (if C1 is later than B1) it should return
Available?
If it is false (not later) then it should return un-available.

try:
=IF(MOD(C1,1)B1,"Available?","Unavailable")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

=IF(C1B1,"Available","Unavailable")

But you don't really need the value in C1. You cold just have

=IF(NOW()B1,"Available","Unavailable")

Help with IF Function for times.
 

Hi Spencer,

Am Thu, 21 Feb 2013 10:10:12 +0000 schrieb Spencer101:

But you don't really need the value in C1. You cold just have

=IF(NOW()B1,"Available","Unavailable")

NOW() is date + time and it is always greater than a time.
Just in this moment NOW is 41326,6111829861
and CTRL+SHIFT+Point = 0,6111829861


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus, thanks very much for your reply.

Your formula works if I remove =now() and manually enter the time.

I misrepresented what I put earlier, I have a D1 cell that contains '=now()' and a C1 that contains '=D1', which is probably pointless.

Is there a correct way of referencing =now() without it impairing the formula?

In regards to =now being '41326,6111829861' what do you mean?

Many Thanks,
Ryan

Edit: Just read about serial number values etc. so I understand that value.

Just realised that it only works after midnight.

A bit more detail, it's a sheet that holds average system availability times for various clients. These systems go down overnight for backups, reporting, stock takes blah blah. Then need to be back up before 6am.
What I what is to open the sheet and it to show me what clients systems are usually available at the current time, hence the 'Available?', it's really just something to do as it can get quite boring here, lol.

Ryan

Help with IF Function for times.
 

Hi,

Am Thu, 21 Feb 2013 23:02:56 +0000 schrieb Ryanoo:

I misrepresented what I put earlier, I have a D1 cell that contains
-'=now()'- and a C1 that contains -'=D1'-, which is probably pointless.

try:
=IF(MOD(C1,1)B1,"Available?","Unavailable")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2