Unoffical empeg BBS

Quick Links: Empeg FAQ | Software | RioCar.Org | Hijack | jEmplode | emphatic
Repairs: Repairs | Addons: Eutronix | Cases

Topic Options
#369744 - 06/11/2017 14:22 Excel help and hello again!
petteri
addict

Registered: 02/08/2004
Posts: 432
Loc: Miami, FL USA
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!

Top
#369745 - 06/11/2017 17:14 Re: Excel help and hello again! [Re: petteri]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5370
Loc: Ajijic, Mexico
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.


Attachments
Hire Date.xlsx (18 downloads)

_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#369746 - 06/11/2017 17:53 Re: Excel help and hello again! [Re: tanstaafl.]
petteri
addict

Registered: 02/08/2004
Posts: 432
Loc: Miami, FL USA
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


Attachments
interview schedule 2017 - sample.xlsx (18 downloads)



Edited by petteri (06/11/2017 17:57)

Top
#369762 - 09/11/2017 10:21 Re: Excel help and hello again! [Re: petteri]
Shonky
pooh-bah

Registered: 12/01/2002
Posts: 1956
Loc: Brisbane, Australia
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.
_________________________
Christian
#40104192 120Gb (no longer in my E36 M3, won't fit the E46 M3)

Top