[[20210521100025]]　『条件付き書式設定で在籍年数を色分け及び65歳の定』（ｍ）　ページの最後に飛ぶ

『条件付き書式設定で在籍年数を色分け及び65歳の定年者の在籍年数に色付け』（ｍ）

条件付き書式設定について教えてください。
設定したい内容2点。
勤続年数に色付と65歳以上の定年退職者に色付けです。
?@勤続年数に関しては、5年、10年、15年、20年、25年、30年、35年、40年、45年までの5年おきに色付けしたいです。
例　勤続年数5年でいうと、5年11カ月までは色付したいが、6年1カ月には色が付かないようにしたいのです。

?A65歳以上の年齢に達した勤続年数に色付けしたいです。

◆現状◆
N5～S161までに勤続年数が入っています。例；5年2カ月3日など
※関数は↓N5～S161までそれぞれ入っています。
DATEDIF($M5,$N$162,"Y")&"年"&DATEDIF($M5,$N$162,"YM")&"ヶ月"&DATEDIF($M5,$N$162,"MD")&"日"

L5～161の縦軸には年齢が入るように関数を入れています。
L5には　=DATEDIF(K5,$L$2,"Y")

M5～161の縦軸には入社年月日が入っています。例1985/4/1

条件付き書式設定を利用して色をつけるにはどのようにしたらよろしいでしょうか。
ご教示ください。

＜使用 Excel：unknown、使用 OS：unknown ＞

＞N5～S161までに
N5～161 までですよね？
K5、L2、N162 にはそれぞれ何が入っているんですか。
（sansuu） 2021/05/21(金) 11:52

説明不足で申し訳ございません。

N5～N161まで
O5～O161まで
P5～P161まで
Q5～Q161まで
R5～R161まで
S5～S161まで
それぞれの縦軸に勤務年数が年度ごとに入っています。

具体的に申し上げますとリフレッシュ休暇制度があり、
それを取得できる勤続年数(5年ごと45年まで)に色を付けたいのです。
基準日は今日の日付ではなく「8/4」までが基準日になります。

K5には生年月日　例1962/9/22
L2には=TODAY()
N162には基準日2018/8/4、O162には2019/8/4　P162には2020/8/4・・・・以降S162まで年度ごとに8/4が入っています。
※この基準日までに勤続年数が5年、10年、15年・・・(45年まで)になる方に色を付けたいのです。
（ｍ） 2021/05/21(金) 12:33

＞それぞれの縦軸に勤務年数が年度ごとに入っています。
さっぱり分かりません。
＞K5には生年月日　例1962/9/22
1個人の場合を想定しているのですか。
（sansuu） 2021/05/21(金) 17:13

Excelの条件付き書式で数式を使う方法｜Office Hack
https://office-hack.com/excel/conditional-formatting-formula/

↑の様なやり方はご理解されていますか?

勤続年数の算出式はご自身把握できおられるし、
同様に年齢も算出可能(あるいは算出済)な状況な訳ですから、

適用順序(プライオリティ)に気を付けつつ、必要な条件付き書式をドンドン設定していけば
それで良さそうに見えますけど...

（白茶） 2021/05/21(金) 17:48

 N5セルからの条件付き書式の条件式

 =AND(MOD(SUBSTITUTE(LEFT(N5,2),"年",""),5)=0,SUBSTITUTE(LEFT(N5,2),"年","")*1<=45)

（半平太） 2021/05/21(金) 20:17

半平太様

私の説明不足でご迷惑お掛け致しました。
完成できました。大変助かりました。
ありがとうございます。

もう一点分かれば教えていただきたいのですが、同シート内の65歳以上の定年退職者のみに色付けです。
年齢が入っているのはN5～N161まで。　関数は　=DATEDIF(K5,$L$2,"Y")　がそれぞれ入っています。

色付けしたいセルは　N5:S161までに表記されている勤続年数のセルに色が入るようにしたいです。

何かご不明点ございましたら恐縮ですがご指摘ください。
宜しくお願いします。

（ｍ） 2021/05/24(月) 10:08

半平太様

追記です。
先ほど成功した勤続年数5年ごとに色付けですが、0年1カ月3日などにも色がついてしまっています。
勤続年数が1年に満たない従業員の色を外すことはできますでしょうか。

ご面倒お掛けしますが宜しくお願いします。
（ｍ） 2021/05/24(月) 10:32

 ちょっと確認

 ＞65歳以上の定年退職者
 これの基準日は？
 L2セル？　それとも 2018/8/4 からの162行目の日付？

 ついでに言っておくと･･･
 5年ごとの条件付き書式
 =AND(MOD(DATEDIF($M5,N$162,"Y"),5)=0,DATEDIF($M5,N$162,"Y")>4,DATEDIF($M5,N$162,"Y")<46)

 さらについでに･･･
 ＞関数は↓N5～S161までそれぞれ入っています。
 ＞DATEDIF($M5,$N$162,"Y")&"年"&DATEDIF($M5,$N$162,"YM")&"ヶ月"&DATEDIF($M5,$N$162,"MD")&"日"
 これも $N$162　→　N$162　にすれば右・下コピーで済むのでは？

 ところで ↓ 同じ人？
[[20210422112140]]『年代及び年代区分を出したいが関数を入れても表示されない』（ｍ）

 以上
（笑） 2021/05/24(月) 14:47

 ＞0年1カ月3日などにも色がついてしまっています。

 0年のケースを失念しました。m(__)m
 以下に変更してください。 
 =AND(MOD(SUBSTITUTE(LEFT(N5,2),"年",""),5)=0,ABS(SUBSTITUTE(LEFT(N5,2),"年","")-25)<=20)

 ＞同シート内の65歳以上の定年退職者のみに色付けです。 
 =$L5>=65

（半平太） 2021/05/24(月) 15:25

 　↓ どっちが正しいのか？
 ＞L5～161の縦軸には年齢が入るように関数を入れています。
 ＞年齢が入っているのはN5～N161まで。

 ＞65歳以上の定年退職者
 年齢はL列で、かつ基準日がL2セルなら
 =$L5>=65

 基準日が162行目の日付なら
 =DATEDIF($K5,N$162,"Y")>=65

 以上
（笑） 2021/05/24(月) 15:30

半平太様
（笑）様

お二方共有難うございます。
私の説明不足で大変お手を煩わせてしましました。

無事に65歳の色付けも、0カ月の色なしもどちらも成功いたしました。
大変助かりましたm(__)m

（ｍ） 2021/05/24(月) 17:29

コメント返信：

[ 一覧(最新更新順) ]

[[20210521100025]] 『条件付き書式設定で在籍年数を色分け及び65歳の定』（ｍ） ページの最後に飛ぶ

[[20210521100025]]　『条件付き書式設定で在籍年数を色分け及び65歳の定』（ｍ）　ページの最後に飛ぶ