Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. Last edited by Ryanoo : February 21st 13 at 05:03 AM Reason: Deleted repeated line. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
|
|||
|
|||
Quote:
But you don't really need the value in C1. You cold just have =IF(NOW()B1,"Available","Unavailable") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
|
|||
|
|||
Quote:
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. Last edited by Ryanoo : February 21st 13 at 11:08 PM |
#6
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speeding up this code- when a custom function is used many times | Excel Programming | |||
Question on the countif function and times | Excel Worksheet Functions | |||
Function for stopwatch times | Excel Worksheet Functions | |||
Need help with custom function that deals with times | Excel Worksheet Functions | |||
How can use IF function more than 7 times in a cell | Excel Worksheet Functions |