Formula tochange cell status when unspecified date activated

August 23, 2016 at 11:59:52
Specs: Windows 7
I need a formula....

I have 3 ranges of cells (365, one for everyday of the year).

In column A I want the cell to say either "CURRENT" or "LEAVER". I want this to say current unless told otherwise from a cell on another page specifying the particular leaving date, which will obviously vary person to person, without changing the status in the cells where the employee was active.

Column B is the dates

Column C is Information I need calculating, but only if the employee status read "CURRENT", which is an easy SUMIF formula.

But column A I am struggling with

So far I have an IF function we that I can gives me both "CURRENT" and "LEAVER" :

=IF('New Starter Info'!$H$2<>"","LEAVER","CURRENT")

But the date changes every cell rather than just the ones after the leaving date.

Any help will be great


See More: Formula tochange cell status when unspecified date activated

Report •

#1
August 23, 2016 at 13:15:53
It is not clear to me what you are trying to accomplish.

re: "So far I have an IF function we that I can gives me both "CURRENT" and "LEAVER" :"

"we that I can gives" I have no idea what you are trying to say in that line.

re: =IF('New Starter Info'!$H$2<>"","LEAVER","CURRENT")

This will return LEAVER if H2 is not empty. Is that not what you want?

re: "But the date changes every cell rather than just the ones after the leaving date."

Once again, I don't know that you are trying to say here.

Perhaps if you posted a small example of your data that would help. Please click on the following line and read the instructions on how to post example data in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 23, 2016 at 13:48:01
Ok, so If I have Dates ranging from 01/01/16 to 31/12/16 in column A and in column B I want the cell to say either "current" or "leaver for that date without me having to input it manually everyday. The cell should say current until I put a leaving date in a box on a seperate page, when I do this I want the cells to stop saying current and start saying leaver but only from the leaving date, all other dates prior should remain as current.

message edited by Cheslop84


Report •

#3
August 23, 2016 at 22:38:36
A B C D
Date
01/01/2016 Start Date 03/01/2016
02/01/2016
03/01/2016 Leave Date 08/01/2016
04/01/2016
05/01/2016
06/01/2016
07/01/2016
08/01/2016
09/01/2016
10/01/2016
11/01/2016
12/01/2016


For example this is my spreadsheet above I want column B to state NA until a start date was inputted in to D3 for that employee, where it will then say current. Then from this start date the status would continue to say current until a leave date was put in to cell D5, when it would then say leaver, without changing any of the previous boxes, just those from the leaving date.


Report •

Related Solutions

#4
August 24, 2016 at 04:02:00
Thank you for this additional information.

It appears that you not read the instructions on how to post example data in this forum. Those instructions can be found via the link at the bottom of this post. Please read those instructions and repost/edit your data so that we can see how your columns are set up.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
August 24, 2016 at 04:45:09
Date	Status		
01/01/2016			Status
02/01/2016			Start Date  2/1/16
03/01/2016			Leave Date
04/01/2016			
05/01/2016			
06/01/2016			
07/01/2016			
08/01/2016			
09/01/2016			
10/01/2016			
11/01/2016			
12/01/2016			


Report •

#6
August 24, 2016 at 04:49:00
The above is just a short example. I want column B to show employment status using start date and leave date cells as their point of reference. Leave date cell will remain blank until the employee leaves therefore dates after the start date need to say CURRENT until a leave date is specified. Then the cells should say LEAVER but from the leave date specifies only

Report •

#7
August 24, 2016 at 04:49:53
Dates prior to start date need to say NA

Report •

#8
August 24, 2016 at 05:51:02
it would have been nice if you had included the Column letters and Row numbers in your example, but I'll try to piece it together based on your earlier posts.

I am going to assume this:

        A             B           C                D
1      Date         Status  		
2   01/01/2016                  Status
3   02/01/2016                  Start Date       2/1/16
4   03/01/2016                  Leave Date
5   04/01/2016			
6   05/01/2016						

If that is correct, try this in B2 and drag it down.

=IF(OR($D$3="",A2<$D$3),"",IF(OR(A2<$D$4,$D$4=""),"Current","Leaver"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
August 24, 2016 at 08:04:28
That works thanks a lot mate that was driving me mad

Report •

#10
August 24, 2016 at 08:25:06
I'm glad it worked. There is one thing that I did not mention:

I answered your specific question which was related to before and after the Start and Leave dates. You may have to modify the formula to deal with matching dates.

In your example of dates which include only the first of the month, dealing with or not dealing with "equal to" situations will result in a month's difference between when "Current" and/or "Leaver" is returned.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question