Excel help and hello again!

Posted by: petteri

Excel help and hello again! - 06/11/2017 14:22

Hello all!

I've not frequented this forum in a while! Catching up on the various threads and all and remembered this not only a nice friendly little corner of the web but a GREAT place for excel help! So as I catch up... blush

I'm looking for a way to fill a cell (green/red) based on time passed from a certain date. Basically I'm working a project where a candidate is eligible for a position as long as they have 6 months experience from hire date. This is really simple and I could just do it manually but I'd like to create a temple file where anyone can just enter in the employee's basic info and the sheet auto-updates.

Any suggestions?

Thanks!
Posted by: tanstaafl.

Re: Excel help and hello again! - 06/11/2017 17:14

Originally Posted By: petteri
candidate is eligible for a position as long as they have 6 months experience from hire date.

If I understand you correctly, you are looking to "green-light" employees who have been working for you for six months or more, with the current "today" date being the reference point.

The attached sample spreadsheet will do that, if the hire dates for the employees are in the same column.

You will want to look in "Conditional Formatting" to see how this works.

tanstaafl.
Posted by: petteri

Re: Excel help and hello again! - 06/11/2017 17:53

Originally Posted By: tanstaafl.
Originally Posted By: petteri
candidate is eligible for a position as long as they have 6 months experience from hire date.

If I understand you correctly, you are looking to "green-light" employees who have been working for you for six months or more, with the current "today" date being the reference point.

The attached sample spreadsheet will do that, if the hire dates for the employees are in the same column.

You will want to look in "Conditional Formatting" to see how this works.

tanstaafl.


Thanks! It won't be from "today" but from position posting date vs hire date. I've been playing around with the DATEDIF formula and I think I might have a solution.

I've looked into conditional formatting and have found a few things there that I can use with the DATEDIF formula. Attached my file. Yes it's a mess I'll need to tidy it up! blush
Posted by: Shonky

Re: Excel help and hello again! - 09/11/2017 10:21

Not fully understanding the exact requirement but Excel "dates" are simply fractional numbers where a unit of 1 is a whole day. 0.5 is 12 hours etc.

So to keep it simple just look for hire dates at least 180 days before the post date?

"Post date - hired date > 180"

And use the $B$32 for your constant (in your DATEDIF) so you can fill down without Excel auto incrementing it.

Or just calculate the "minimum" hire date (B32 = B33-180) and compare directly with the hire date.