Stilts15ak
New Member
- Joined
- Jun 6, 2015
- Messages
- 4
Hello All,
I’m hoping someone might be able to help adding on to a formula I have working well, but want to improve. I am having trouble with figuring out the syntax.
I currently have a document for keeping track of employees. The biggest thing that I want to do is keep track of the duration of their contracts and when they are starting and ending. See Screen Shot. NOTE: Other information in other columns removed as it is not pertinent to questions below.
This formula for DAYS LEFT in the “L Column”:
works as such that it calculates the days left of the individuals contract from their start date. However, if their start date is after the start date of the project in E1, it displays the same duration amount of the contract with an asterisk at the end (THIS IS INTENTIONAL). Also if no date is inputted into the CONTRACT START and CONTRACT END, then the DURATION & DAYS LEFT COLUMN display “No Date(s)” (THIS IS INTENTIONAL).
Formula for DURATION column is:
. This works fine. But I am including for full context in regards to the question of this post.
Currently, the DAYS LEFT column reports back negative numbers once the CONTRACT END date is past today’s date (EXCEL DOING BY DESIGN). I don’t want to have negative numbers. I would rather the DAYS LEFT column show “Finished”, indicating that the person’s contract has been complete.
I have tried to insert this
into the working formula above, but cannot get the syntax right.
I have tried these versions, but they don’t work:
1)
2)
Any assistance anyone can provide would be greatly appreciated.
Thank you!
I’m hoping someone might be able to help adding on to a formula I have working well, but want to improve. I am having trouble with figuring out the syntax.
I currently have a document for keeping track of employees. The biggest thing that I want to do is keep track of the duration of their contracts and when they are starting and ending. See Screen Shot. NOTE: Other information in other columns removed as it is not pertinent to questions below.
This formula for DAYS LEFT in the “L Column”:
Excel Formula:
=IF(G5>TODAY(),K5&"*",IF(AND(G5<>"",I5<>""),I5-TODAY(),"No Date(s)”))Formula for DURATION column is:
Excel Formula:
=IF(AND(G5<>"",I5<>""),DAYS(I5,G5),"No Date(s)”)Currently, the DAYS LEFT column reports back negative numbers once the CONTRACT END date is past today’s date (EXCEL DOING BY DESIGN). I don’t want to have negative numbers. I would rather the DAYS LEFT column show “Finished”, indicating that the person’s contract has been complete.
I have tried to insert this
Excel Formula:
=IF(AND(G9<>"",I9<>"),AND(G9<TODAY(),I9<TODAY()"),"Finished")I have tried these versions, but they don’t work:
1)
Excel Formula:
=IF(I5<TODAY(),"Finished",IF(G5>TODAY(),K5&"*",IF(AND(G5<>"",I5<>""),I5-TODAY(),"No Date(s)")))2)
Excel Formula:
=IF(IF(G5>TODAY(),K5&"*",IF(AND(G5<>"",I5<>""),I5-TODAY(),"No Date(s)")), IF(I5<TODAY(),"Finished"),"")Any assistance anyone can provide would be greatly appreciated.
Thank you!
