agwalsh
asked on
time formatting in formula
hi Folks
I've created this formula...the idea being that if the status column shows less than 5 minutes and H3 is not the same as h2, it should show up as transient
=IF(AND(H3<>H2,TEXT([@Stat us],"[mm]: ss")<5),"t ransient", "resident" ). However when I copy down the formula, everything is showing as resident and I think it's to do with the 5 in my formula. I've formatted the status column as time.
How can I show my 5 as five minutes or is there something else?
Thanks as always..
I've created this formula...the idea being that if the status column shows less than 5 minutes and H3 is not the same as h2, it should show up as transient
=IF(AND(H3<>H2,TEXT([@Stat
How can I show my 5 as five minutes or is there something else?
Thanks as always..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Have awarded you all points because you all helped to the solution - which eventually I did as:
=IF(AND(H5=H4,[@[Time Elapsed]]>TIME(0,5,0)),"re sident","t ransient")
I gave BarryHoudini best solution because his answer was the closest to what I eventually got working for me. But THANK YOU as always to all of you :-)
=IF(AND(H5=H4,[@[Time Elapsed]]>TIME(0,5,0)),"re
I gave BarryHoudini best solution because his answer was the closest to what I eventually got working for me. But THANK YOU as always to all of you :-)
ASKER
Your formula was:
=IF(AND(H3<>H2,ROUND(C9*14
simply because c9 wasn't a relevant cell
I adjusted it to:
=IF(AND(H4<>H3,ROUND([@Sta
Unfortunately this is still giving me "resident status"
I also formatted Status as "[HH]:mm" but I'm still getting all Residents.
Would the following have any impact? I've used =TEXT(A3-A2,"[h]:mm:ss") to extract the Time Elapsed and =TEXT([@[Time Elapsed]],"[HH]:mm") to show Status..
I'm attaching a sample file to illustrate. Thanks for all your help. Much appreciated.
I've tried all your approaches as you can see in the file but reckon I'm missing something in my time formatting..
EE--If-on-time.xlsx