[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『有給残日数(残時間)一覧表の作成』（TIMTAM）
似たような質問もあり、応用しようと思いましたがうまくいかず数日悩んでおります。
みなさまの知恵をお貸しください。
全社員の有休取得一覧表を作成しておりますが、
どういう数式を入れ込んだら残日数や残時間が下記のように反映されるでしょうか。
条件としては‥
・就業時間は7時間30分だが時間有給は8時間取得できる
・前年度繰越日数は20日が上限(20日と〇時間余っていても20日のみしか繰り越せない)
※当年使用可能日数は最大40日(時間有給3日分を含む)なので。
・前年度繰越時間は8時間が上限
・当年付与日数は20日
・時間有給を1時間使用すると日数から1日(8時間)もってくる
・時間有給は24時間(3日分)が上限
・半日有給も有り(有給0.5日でも、時間有給を3h(4h)でもOK)
現在作成中のレイアウトは
A B C D E F G H 1 氏名 前年度繰越日数 前年度繰越時間 当年付与日数 当年取得日数 当年取得時間 残日数 残時間 2 A氏 0.5 0 20 18.5 7 1 1 3 B氏 2.5 5 20 17.5 3 5 2 4 C氏 18.5 2 20 12.5 3 25 7
B氏の残時間(H3)は繰越時間(C3)5時間-取得時間(F3)の3時間=2時間となっております。
前年に繰越時間がある人はそれから使用し、24時間まで使えます。 複雑ですがみなさま宜しくお願いします。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
こんばんは ＾＾ 年休。。。ふくざつですよね〜 中途半端にやればかえって手間が増える場合もなきにしもあらず ですね。間違えるともぉ〜大変！、個人別有給管理台帳、御社の 勤続期間別給付日数一覧表等との連動等が不可欠な気がいたしま す。【年休管理システムの作成方法】等で検索すればたくさん紹 介して下さっているサイトがありましたですよ。、いずれにしま してもこちらでチャチャっと。。。と云う規模では無さそぉなき がいたします。 ← わたしだけかも (#^.^#) 気が付いた点 のみですみません。他の回答者様のアドバイスをお待ちください ね。。。頑張ってくださいませ。 m(_ _)m （隠居じーさん） 2020/02/19(水) 18:04
全然違うかもです。自信なし。
G2 =INT(MIN(320,((MIN(160,8*B2+C2)+D2*8)-(E2*8+F2)))/8) I2 =MIN(320,((MIN(160,8*B2+C2)+D2*8)-(E2*8+F2)))-(8*G2) （GobGob） 2020/02/19(水) 18:57
GobGobさん
ご回答ありがとうございました。例にあげたA氏〜C氏はその計算式で無事に求めていた答えがでましたが
他の人では使えませんでした。
A B C D E F G H
1 氏名 前年度繰越日数 前年度繰越時間 当年付与日数 当年取得日数 当年取得時間 残日数 残時間
2 D氏 20 0 20 15.5 10 22.5 6
上記のようになってほしいのにそちらの数式では残日数が23日と残時間が2時間と出てしましました。
複雑な式を解読するのに時間がかかってしましお返事が遅くなりました。
せっかくご回答いただいたのに申し訳ありません。
（TIMTAM） 2020/02/20(木) 10:45
いくつか質問 （年初リセットの考え方）
前年繰り越し時間が8時間、繰り越し日数が20日として、 本年度付与が20日（日数のみ）とする場合、
40日（日数のみ 上限MAX）が年初となるのか？ 39日 + 8時間(1日）が年初となるのか？
どっちっすか？
前年繰り越し時間 4時間(半日分）、繰り越し10日として 本年度付与が20日（日数のみ）とする場合
30.5日（日数のみ）が年初となるのか？ 30日 + 4時間 が年初となるのか？
どっちっすか？
（GobGob） 2020/02/20(木) 12:48
前年繰り越し時間が8時間、繰り越し日数が20日として、 本年度付与が20日（日数のみ）とする場合、 40日（日数のみ 上限MAX）が年初となるのか？ 39日 + 8時間(1日）が年初となるのか？
→40日（日数のみ 上限MAX）が年初です。
前年繰り越し時間 4時間(半日分）、繰り越し10日として 本年度付与が20日（日数のみ）とする場合 30.5日（日数のみ）が年初となるのか？ 30日 + 4時間 が年初となるのか？
→30日 + 4時間 が年初です。
ややこしいですよね、不可能な気がしてきました。
宜しくお願いします
（TIMTAM） 2020/02/20(木) 13:12
主です。FとGの間に日数換算する行を設けてみました。 数式は以下の通りです。
・A〜F 手入力 ・G3 =E3+ROUNDDOWN(F3/8,0) ・H3 =MOD(F3,8) ・I3 =(D3+B3)-(G3+IF(C3=0,COUNTIF(H3,">=1"),0)) ・J3 =IF(MOD(F3,8)=0,"0",IF(C3>F3,C3-F3,8-H3))
A B C D E F G H I J
1 氏名 前年度 前年度 当年付与 当年取得 当年取得 時間を日数換算した 残日数 残時間
2 繰越日数 繰越時間 日数 日数 合計日数 合計時間
3 A氏 0.5 0 20 18.5 7 18.5 7 1 1
4 B氏 2.5 5 20 17.5 3 17.5 3 5 2
5 C氏 18.5 2 20 12.5 3 12.5 3 26 5
6 D氏 20 0 20 15.5 10 16.5 2 22.5 6
C氏だけうまくいきませんでした‥B氏の残日数は「25日」残時間は「7時間」です あと少しなのに悔しい‥ （TIMTAM） 2020/02/20(木) 15:49
またまた自信なしですが。
A B C D E F G H I J 1 氏名 前年日 前年時 付与日 年初日 年初時 当年日 当年時 残日数 残時間 2 A氏 0.5 0 20.0 20.5 0 18.5 7 1.0 1 3 B氏 2.5 5 20.0 22.5 5 17.5 3 5.0 2 4 C氏 18.5 2 20.0 38.5 2 12.5 3 25.0 7 5 D氏 20.0 0 20.0 40.0 0 15.5 10 22.5 6 6 E氏 10.0 6 20.0 30.0 6 10.0 16 17.0 14 7 F氏 21.0 3 20.0 40.0 0 5.0 20 32.0 4 8 G氏 18.5 2 20.0 38.5 2 12.5 1 26.0 1
2列追加（E、F列）
E2 =IF(B2*8+C2+D2*8>=320,40,B2+D2) F2 =IF(E2=40,0,SUM(B2,D2)*8+C2-E2*8)
残数計算 I2 =E2-G2-(INT(H2/8)+(F2<H2)) J2 =(E2*8+F2)-(G2*8+H2)-(I2*8) （GobGob） 2020/02/20(木) 15:54
あっ。E氏が。。。
再考ですな。 （GobGob） 2020/02/20(木) 15:56
A B C D E F G H I J 1 氏名 前年日 前年時 付与日 年初日 年初時 当年日 当年時 残日数 残時間 2 A氏 0.5 0 20.0 20.5 0 18.5 7 1.0 1 3 B氏 2.5 5 20.0 22.5 5 17.5 3 5.0 2 4 C氏 18.5 2 20.0 38.5 2 12.5 3 25.0 7 5 D氏 20.0 0 20.0 40.0 0 15.5 10 22.5 6 6 E氏 10.0 6 20.0 30.0 6 10.0 16 17.0 8 7 F氏 21.0 3 20.0 40.0 0 5.0 20 32.0 4 8 G氏 18.5 2 20.0 38.5 2 12.5 1 26.0 1
I2 =E2-G2-INT((J2+H2)/8) J2 =MIN(24,8*(INT(H2/8)+(F2<H2))+F2)-H2
※検証不足してるやろなぁ。。。。 （GobGob） 2020/02/20(木) 16:10
何度もありがとうございます。
C氏の残日数は25日、残時間は7hなのでうまくいきませんでした‥
E氏も残日数は19日で残時間は6h、
F氏も残日数33日で残時間は4hで表示させたいです。
他の社員にも反映させたところ、半分くらいうまくいってないです。
前年度繰越時間がある人は、まずはそれから消化して上限24時間まで。
というところが鬼門ですかね
（TIMTAM） 2020/02/20(木) 17:14
>C氏の残日数は25日、残時間は7hなのでうまくいきませんでした‥
そうなってるんですが。。。。
>前年度繰越時間がある人は、まずはそれから消化して上限24時間まで。
こんなルール。初耳です。。。。 （GobGob） 2020/02/21(金) 14:29
すこし整理。
＞E氏も残日数は19日で残時間は6h、 ＞F氏も残日数33日で残時間は4hで表示させたいです。
【E氏】 日 時間 繰越 10.0 6 付与 20.0 START 30.0 6
取得 10.0 16
残日 18.0 ⇒ 30 -10 - 2(時間に2日分もっていくので) = 18.0 残時間 6 ⇒ 10時間足りない ⇒ 日から2日分もってくる(16時間) 16-10 = 6H
なので E氏は 「18日＋6時間が正解」 でいい？
【F氏】 日 時間 繰越 21.0 3 付与 20.0 START 40.0 0 ⇒ 上限が40日なので。
取得 5.0 20
残日 32.0 ⇒ 40 - 5 - 3(時間に3日分もっていくので) = 32.0 残時間 4 ⇒ 20時間足りない ⇒ 日から3日分もってくる(24時間) 24-20 = 4H
なので F氏は 「32日＋4時間が正解」 でいい？
（GobGob） 2020/02/21(金) 16:34
A B C D E F G H I J 1 氏名 前年日 前年時 付与日 年初日 年初時 当年日 当年時 残日数 残時間 2 A氏 0.5 0 20 20.5 0 18.5 7 1.0 1 3 B氏 2.5 5 20 22.5 5 17.5 3 5.0 2 4 C氏 18.5 2 20 38.5 2 12.5 3 25.0 7 5 D氏 20 0 20 40 0 15.5 10 22.5 6 6 E氏 10 6 20 30 6 10 16 18.0 6 7 F氏 21 3 20 40 0 5 20 32.0 4 8 G氏 18.5 2 20 38.5 2 12.5 1 26.0 1
E:Fはそのまま
I2 =E2-G2-INT((J2+H2)/8) J2 =IF(F2>=H2,F2-H2,8-MOD(ABS(F2-H2),8)) （GobGob） 2020/02/21(金) 16:36
>C氏の残日数は25日、残時間は7hなのでうまくいきませんでした‥ >そうなってるんですが。。。。
C氏はうまくいってましたね、すみません
>前年度繰越時間がある人は、まずはそれから消化して上限24時間まで。 >こんなルール。初耳です。。。
一番最初に質問した際に記載してます
>なので E氏は 「18日＋6時間が正解」 でいい？ >なので F氏は 「32日＋4時間が正解」 でいい？
すみません。E氏もF氏もそれが正解です。
--------------------------------------------------
最終的にお返事いただいた数式を当てはめてみました。
全員分確認したところ、 前年度繰越時間がない人で、 今年度時間有給をキレイに取得している人 (言い方変ですが、8h,16h,24hいずれかちょうどを取得している人)の 残時間(8hと表示されている)を、日数として1日持ってくることは可能でしょうか。
[ご提案いただいた数式を当てはめた場合] A B C D E F G H I J 1 氏名 前年日 前年時 付与日 年初日 年初時 当年日 当年時 残日数 残時間 2 〇〇 20 0 20 40 0 12 16 25 8
Jの残時間8hを「0h」とし、Iの残日数の方に「26日」と表示させたいです。
上記のパターン以外はすべて完璧でした！！(感動)
（TIMTAM） 2020/02/25(火) 16:14
逆質問だけど。
例えば 年初時間が「16」 当年取得が「8」の場合は「0」なの？「8」なの？ （GobGob） 2020/02/26(水) 13:19
＞前年度繰越時間は8時間が上限
あー。間違い、質問訂正
例えば 年初時間が「8」 当年取得が「0」の場合は「0」なの？「8」なの？
（GobGob） 2020/02/26(水) 13:22
A B C D E F G H I J 1 氏名 前年日 前年時 付与日 年初日 年初時 当年日 当年時 残日数 残時間 2 A氏 0.5 0 20 20.5 0 18.5 7 1.0 1 3 B氏 2.5 5 20 22.5 5 17.5 3 5.0 2 4 C氏 18.5 2 20 38.5 2 12.5 3 25.0 7 5 D氏 20.0 0 20 40.0 0 12 16 26.0 0 ⇒ 残時間が8の倍数なので「0」 6 E氏 10.0 6 20 30.0 6 10 16 18.0 6 7 F氏 21.0 3 20 40.0 0 5 20 32.0 4 8 G氏 18.5 2 20 38.5 2 12.5 1 26.0 1 9 H氏 12.0 8 20 32.0 8 12 0 19.0 8 ⇒ 年初が8時間ｽﾀｰﾄなので「8」
E2,F2,I2はそのまま。
J2 =IF(SUM(F2,H2)<24,IF(F2>=H2,F2-H2,IF(MOD(F2-H2,8),8-MOD(H2-F2,8),0)),0) （GobGob） 2020/02/26(水) 17:05
よく考えてみたら年初時間「8」が上限はありえないですね。 すみません、指摘されて気が付きました。
「1〜7」時間しか繰り越せないです。
年初時間が「7」 当年取得が「0」の場合は翌年度繰越時間は「7」 年初時間が「4」 当年取得が「24」の場合は翌年度繰越時間は「4」 年初時間が「0」 当年取得が「16」(2日分)の場合は翌年度繰越時間は「0」です。 ↑あと8h取得できますが、それは「1」日として翌年度繰越日数になります。
もちろん20日付与後40日超えなら、時間も切り捨てられ「40」日が年初日です。 （TIMTAM） 2020/02/26(水) 17:10
新しい数式をいれてみました！ 8時間の残時間がきちんと日数の方に移動しました！
しかし下記のように表示させたいが
A B C D E F G H I J
1 氏名 前年日 前年時 付与日 年初日 年初時 当年日 当年時 残日数 残時間
2 〇〇 16.5 4 20 36.5 4 12 24 21.5 4
このパターンの人にいれこむと残日数が「21.5」日残時間が「0」となってしまいました‥ （TIMTAM） 2020/02/26(水) 17:19
ルールが
> 時間有給は24時間(3日分)が上限
なので当年24時間取得したら残0だと思うんやけど… （GobGob） 2020/02/26(水) 23:49
一応、
J2 =IF(H2<24+F2,IF(F2>=H2,F2-H2,IF(MOD(F2-H2,8),8-MOD(H2-F2,8),0)),0)
やっぱりいまいちルールがはっきりしない。。。
時点のルール整理
・就業時間は7時間30分だが時間有給は8時間取得できる ⇒ OK
・前年度繰越日数は20日が上限(20日と〇時間余っていても20日のみしか繰り越せない) ※当年使用可能日数は最大40日(時間有給3日分を含む)なので。 ⇒ OK
・前年度繰越時間は8時間が上限 ⇒ 【7時間以内】
・当年付与日数は20日 ⇒ OK
・時間有給を1時間使用すると日数から1日(8時間)もってくる ⇒ 【8時間持ってきた分を使い切ってからさらに使用すると・・・って意味？】
・時間有給は24時間(3日分)が上限 ⇒ 【繰越時間をまず消化してから付与分を消化】＋【当年取得は ・・・ 繰越時間 + 24時間が上限 ・・・ ？】
・半日有給も有り(有給0.5日でも、時間有給を3h(4h)でもOK) ⇒ ？？？？
※ところで、最後のルール、0.5日分も持ってくるの？？？？ （GobGob） 2020/02/27(木) 08:03
I2 =E2-G2-IF(F2<H2,ROUNDUP((H2-F2)/8,0),0) J2 =(E2*8+F2)-(G2*8+H2)-(I2*8)
コッチの方がスッキリしてるかｗ。
※「当年取得時間 24時間以内」の処理はしてまへん。入力者に委ね。 （GobGob） 2020/02/27(木) 09:02
> ・時間有給を1時間使用すると日数から1日(8時間)もってくる ⇒ 【8時間持ってきた分を使い切ってからさらに使用すると・・・って意味？】
そうです。そういう意味です。
ex)年初日 20日 - 取得時間 1h = 残日数19、残時間7h ですよね
毎日1hの時間有給を1週間取り続けると 残日数19、残時間が6h..5h..4h..3h..2h..1hとなり、8時間使い切ったら次は
残日数から1日(8時間)を残時間に持ってきて、残日数18、残時間7h..6h..5h..となっていきます。
>・時間有給は24時間(3日分)が上限 ⇒ 【繰越時間をまず消化してから付与分を消化】＋【当年取得は ・・・ 繰越時間 + 24時間が上限 ・・・ ？】
違います。ここややこしいですよね。 【繰越時間をまず消化してから付与分を消化】＋【当年取得は ・・・ 繰越時間と取得時間を合わせて24時間です】
ex)<2019年度> 年初時 4 当年時24 =残時4 <2020年度> 年初時 4 当年時24 =残時4
毎年24時間使い切ると一生4hが残っているように見えますが、 「<2019年度> 年初時 4」は2018年度分の残った4hであり、 「<2020年度> 年初時 4」は2019年度分の残った4hということです。(前年繰越時間をまず消化してから‥という概念から)
>・半日有給も有り(有給0.5日でも、時間有給を3h(4h)でもOK) ⇒ ？？？？ >※ところで、最後のルール、0.5日分も持ってくるの？？？？
すみません、読解力がなくて回答になってないかもしれませんが。
ex)・午前中お休みする場合(日数で処理)
年初日 20.5日 - 取得日数 0.5日 = 残日数20日 ←単純
・午前中お休みする場合(時間有給で処理)
年初日 20.5日 - 取得時間 3h = 残日数19.5日、残時間 5h
このような形になります。
最終版の数式をいれてみました 全員無事に正解が導き出せました！ この作業をやっと手放せるので感動しています。 GobGobさんにはうちの社から感謝状をあげたいくらいです。 (その前に勤怠システム改修の依頼をしろって感じですが)
長期間に渡り、ありがとうございました泣 （TIMTAM） 2020/02/27(木) 11:52
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.