Excel2003で年次有給休暇(個人)の管理をするための数式を教えてください。
今年度から勤務時間の改正により1日が7時間45分となりました。
有給休暇は1日単位で使用すると7時間45分で計算され、
時間休で使用すると1時間60分で計算されます。
なので、1日取得すると、例えば有給休暇全体が40日0時間であれば39日0時間。
この状態から1時間の時間休を取得すると38日6時間45分となります。
そして、38日0時間45分から1時間の時間休で37日7時間30分となります。
つまり、1日を7時間45分とし、
日単位では7時間45分、時間単位では60分で計算される仕組みです。
今までは、D列に取得日数、E列に取得時間、F列に残り総日数、G列に取得累計日数として、
成数が日数、少数部分が時間として表示させていました。
勤務時間が8時間なので、8進法を使っていました。
分かりにくいのですが、参考までに以前まで使っていた数式を載せます。
F4セル:前年度からの繰り越し休暇日数(例えば20と入力)
B8・C8:申請年月日・曜日(入力欄)
D8・E8:取得日数・時間(入力欄)
F8:有給休暇の残り日数
=IF(B8="","",IF(20*8+F4*8-(D8*8)-E8<64,DEC2OCT(20*8+F4*8-(D8*8)-E8),IF(20*8+F4*8-(D8*8)-E8<128,DEC2OCT(20*8+F4*8-(D8*8)-E8)-20,IF(20*8+F4*8-(D8*8)-E8<192,DEC2OCT(20*8+F4*8-(D8*8)-E8)-40,IF(20*8+F4*8-(D8*8)-E8<256,DEC2OCT(20*8+F4*8-(D8*8)-E8)-60,IF(20*8+F4*8-(D8*8)-E8<320,DEC2OCT(20*8+F4*8-(D8*8)-E8)-80,DEC2OCT(20*8+F4*8-(D8*8)-E8)-100)))))/10)
G8:その年度の有給休暇取得日数累計
=IF(B8="","",IF(B8="","",IF(D8*8+E8<64,DEC2OCT(D8*8+E8),IF(D8*8+E8<128,DEC2OCT(D8*8+E8)-20,IF(D8*8+E8<192,DEC2OCT(D8*8+E8)-40,IF(D8*8+E8<256,DEC2OCT(D8*8+E8)-60,IF(D8*8+E8<320,DEC2OCT(D8*8+E8)-80,DEC2OCT(D8*8+E8)-100))))))/10)
今までは、残り10日と3時間であれば10.3と表示させていました。
今回からは、これを一つのセルに「10日3時間0分」と表示させたいと考えています。
検索サイトで、いくつか数式の紹介があったのですが、
どうも上手に計算・表示させることができなくて困っています。
よろしくお願いします。
No.7ベストアンサー
- 回答日時:
No.4です!
またまた・・・横からお邪魔します。
有給の残日数セルは単純に日数の数値のみを入力させたいと思いますので、
もう一度画像をアップしたいと思います。
今回は累計の列も数式を作ってみました。
F8セルに
=IF(B8="","",INT((($F$4-SUM($D$8:D8))*"7:45"-SUM($E$8:E8))/"7:45")&"日"&TEXT(ROUND(MOD(($F$4-SUM($D$8:D8))*"7:45"-SUM($E$8:E8),"7:45"),5),"[h]時間mm分"))
累計のG8セルに
=IF(B8="","",INT((SUM($D$8:D8)*"7:45"+SUM($E$8:E8))/"7:45")&"日"&TEXT(MOD(ROUND(SUM($E$8:E8),5),"7:45"),"[h]時間mm分"))
という数式を入れ、F8・G8セルを範囲指定し、G8セルのフィルハンドルで下へコピーすると
画像のような感じになります。
以上、今回は参考になれば良いのですが・・・
何度も失礼しました。m(__)m
何度も何度もありがとうございました。
皆様のおかげで年休整理ファイルが完成できました。
最後はTEXT関数の使い方が分かっていれば良かったのですね…。
他にも間違っていた箇所あったようで、本当に助かりました。
完成したシートの最終的な数式を書きます。
(1) G4:前年度からの繰り越し日時数(入力形式=日数+"時:分")
ここは表示形式がd日h時間m分になっているので、後でTEXT関数で表示させようと思います。
(2) B~E列:入力欄
取得月日、曜日、取得日数、取得時数(入力形式:1日ならD列に「1」、1時間ならE列に「1」)
(4) D列:残日時数
=IF(B8="","",INT(((20+$G$4-SUM($D$8:D8))*"7:45"-SUM($E$8:E8)/24)/"7:45")&"日"&TEXT(ROUND(MOD(($G$4-SUM($D$8:D8))*"7:45"-SUM($E$8:E8)/24,"7:45"),5),"[h]時間mm分"))
(5) E列:今年度累計取得日時数
=IF(B8="","",INT((SUM($D$8:D8)*"7:45"+SUM($E$8:E8)/24)/"7:45")&"日"&TEXT(MOD(ROUND(SUM($E$8:E8)/24,5),"7:45"),"[h]時間mm分"))
たくさんの方々にアドバイスを頂き本当にありがとうございました。
No.10
- 回答日時:
回答No8です。
F4セルには例えば20と入力します。F8セルには1日なら1のように数値を入力します。E8セルには1時間なら1のように数値を入力します。
答えは何日何時間何分のように表示されます。
数式のご紹介をありがとうございました。
早速Excelに入力したところ、イメージした通りの形式で表示されています。
皆様からいくつかの数式を紹介していただき、
違う部分と似ている部分がありますが、
今の自分にはどこに違いが出てくるのかは分かりません。
これから関数や時間の計算について調べた上で、
自分の分かりやすいような数式を見つけていこうと思います。
この質問で随分勉強させていただきました。
ありがとうございました。
No.9
- 回答日時:
回答No8です。
F4セルには例えば20と入力します。F8セルには1とか2などの数値を入力します。E8セルには1時間なら1のように数値を入力します。
答えは何日何時間何分のように表示されます。
No.8
- 回答日時:
F8セルには次の式を入力して下方にオートフィルドラッグします。
=IF(OR(F$4="",COUNT(D8:E8)=0),"",INT(((F$4-SUM(D$8:D8))*"7:45"-SUM(E$8:E8)*"1:00")/"7:45")&"日"&INT(MOD(((F$4-SUM(D$8:D8))*"7:45"-SUM(E$8:E8)*"1:00"),"7:45")/"1:00"+0.001)&"時間"&ROUND(MOD((MOD(((F$4-SUM(D$8:D8))*"7:45"-SUM(E$8:E8)*"1:00"),"7:45")/"1:00"+0.001),1)*60,0)&"分")
G8セルには次の式を入力して下方にオートフィルドラッグします。
=IF(OR(F$4="",COUNT(D8:E8)=0),"",INT(((SUM(D$8:D8))*"7:45"+SUM(E$8:E8)*"1:00")/"7:45")&"日"&INT(MOD(((SUM(D$8:D8))*"7:45"+SUM(E$8:E8)*"1:00"),"7:45")/"1:00"+0.001)&"時間"&ROUND(MOD((MOD(((SUM(D$8:D8))*"7:45"+SUM(E$8:E8)*"1:00"),"7:45")/"1:00"+0.001),1)*60,0)&"分")
No.6
- 回答日時:
なるほど。
列幅をぐぐっと狭めて,日数部分が39だけの表示桁数になるように調整してみてください。
G/標準"日"h"時間"mm"分"
と設定した方が,桁が動かなくてずれなくて良いですね。
なお狭めすぎると######表示になります。
あるいは表示の制約を回避したいときは,やはり他の方から寄せられた回答の方式で
=IF(D8+E8,INT(INT($F$4)-SUM($D$8:D8)-ROUNDUP(SUM(-MOD($F$4,1),$E$8:E8)/"7:45",0))&TEXT(MOD("7:45"-SUM(-MOD($F$4,1),$E$8:E8),"7:45"),"日h時間m分"),"")
などのようにします。
何度もありがとうございました。
keithin様の数式と、
ここでいただいた数々のご助言や数式を参考に、
ほぼ表が完成しました。
自分で数式を変更した点としては、
前年度繰り越しは変更する可能性がありますが、
今年度から新しく付与される日数は20日なので、
この部分を数式に入れてみました。
また、時間休は分で取得することはないので、
「1:00」という入力ではなく、単に「1」という入力でできるよう、
ご助言を見ながら挑戦してみました。
そして、表示形式を標準にして表示されるようにしました。
その結果、F列(残り有給休暇)は、
=IF(D8+E8,INT((20+$F$4-SUM($D$8:D8)-SUM($E$8:E8)/24))&"日"&TEXT(MOD(20*"7:45"+$F$4*"7:45"-SUM($D$8:D8)*"7:45"-SUM($E$8:E8)/24,"7:45"),"[h]時間mm分"),"")
数式の半分位の意味は分かってはいないのですが、
これで今のところ正しく表示されています。
そして、G列(今年度取得日時数)です。
最初にkeithin様から教えていただいた数式から、
入力形式の変更による補正をして、
=IF(D8+E8,SUM($D$8:D8)+INT(SUM($E$8:E8)/24/"7:45")+MOD(SUM($E$8:E8)/24,"7:45"),"")
としてみました。
この数式で今のところ矛盾なく表示されています。
ただ、この数式では、表示形式をG/標準"日"h"時間"mm"分"にしているため、
日数部分が、0日であれば問題なく、一桁の数字の時は小数表示になります。
そして、列幅を調整することで回避できますが、
そうすると日数が二桁のところで####という表示になってしまいます。
この問題を解決するために、色々と試しているのですが、
1~7時間までは良いのですが、
8時間取得したところで0日0時間15分となったり、
なかなかうまくいきません。
ほぼ完成しているんですけど、、、
もしよろしければ解決策があればお願いしたいのですが…。
No.5
- 回答日時:
回答No1です。
>前年度から繰り越される日数や時間も変動する可能性があることに気づきました。
F4の記述が「18日6時間45分」等だった場合に計算に誤りがありました。失礼しました。
D列に日休を整数で入力として
E列に時間給を時:分で入力として
F8:
=IF(D8+E8,INT($F$4)-SUM($D$8:D8)-ROUNDUP(SUM(-MOD($F$4,1),$E$8:E8)/"7:45",0)+MOD("7:45"-SUM(-MOD($F$4,1),$E$8:E8),"7:45"),"")
G8は変更有りません。
=IF(D8,SUMIF($D$8:D8,">=1")+INT(SUMIF($D$8:D8,"<1")/"7:45")+MOD(SUMIF($D$8:D8,"<1"),"7:45"),"")
>40日だと8日になる
なるほど,それは思わぬ落とし穴でした。
FG列のセルの表示形式を
G/標準"日"h"時間"m"分"
としてみてください。
併せてF4の記入方法について,前年度からの繰り越し込みで例えば「35日5時間45分」のような場合
=35+"5:45"
のように記入してみてください。
表示形式の「d」を使うと1月31日を超えると2月になってしまい,8などで表示されます。
他に寄せられている回答は計算結果の値に「年月日 時分秒」形式を採用していないため,この問題を回避できています。
何度も本当にありがとうございます。
なるほど、表示形式の問題だったのですね。
それで、F4を40にして、
取得時間を1:00とし、
教えていただいた通りに数式を入力し、
表示形式のdをG/標準にすると、
F8の日数の部分が39.28と表示されます。
表示形式を色々と変えて試してみたのですが、
なかなか上手に表示されません。
今日はこれで作業を中断して、
また明日、他の回答者様の数式とkeithin様の数式を組み合わせて
色々と試しながらやってみようと思います。
それでも、今日の質問で随分前進できました。
お手数をおかけしましたが、本当にありがとうございました。
No.4
- 回答日時:
こんばんは!
外していたらごめんなさい。
勝手に↓の画像のように表を作ってみました。
実際には実数としての数値が必要だと思いますので、
仮に残り日数が40日の場合 G4セルに
="7:45"*40 としてセルの表示形式をユーザー定義から
[h]:mm としています。
この数値を○日○時間○分というようにF4セルに表示させています。
F4セルは
=INT(G4/"7:45")&"日"&TEXT(ROUND(MOD(G4,"7:45"),5),"[h]時間mm分")
という数式を入れています。
このF4セルは単なる飾りで実際はG4セルが必要になります。
そしてF8セルに
=IF(B8="","",INT((G4-D8*"7:45"-E8)/"7:45")&"日"&TEXT(MOD(G4-D8*"7:45"-E8,"7:45"),"[h]時間mm分"))
という数式を入れたら画像のような感じで表示されるはずです。
以上、参考になれば良いのですが
的外れならごめんなさいね。m(__)m
ご回答ありがとうございました。
全く的外れではなく、教えていただいた通りに入力すると、
確かに分かりやすい表示になります。
半分あきらめていたのですが、色々な方法があることにびっくりしています。
実際に入力しながら、
通常の数字の扱いと時間の関数を用いた数字の扱いとの違いが分かってきました。
また、表作りを始めて、前年度から繰り越される日数や時間も変動する可能性があることに気づきました。
ここからは時間の計算について、勉強してみます。
使うべき関数や勉強のしどころも分かり、本当に助かりました。
ありがとうございました。
No.3
- 回答日時:
以下のような数式で本年度の有休残日数および時間数を表示できます。
=INT(本年度有給休暇数-取得週休休暇日数-取得有休時間数/"7:45")&"日"&TEXT("7:45"-MOD(取得有休時間数,"7:45"),"h時間m分")
取得有休時間数が「2:30」のような形式で入力されていない場合は、たとえば「2.5」と入力しているなら上記の数式の時間の部分を24で割り算してください。
もし、それ以外の方式で入力している場合は、実際に使用している入力形式を具体的に提示してください。
早速のご回答ありがとうございました。
当初は時間の入力を「1」、「2」と入力することを考えていました。
ただ、入力形式を「1:00」とするのでも大丈夫です。
Excelでの時間の計算を使うというのがポイントだったんですね。
教えていただいた数式をExcelの表と照らし合わせながら入力してみたら、
イメージ通りの表示になりました。
時間の計算について勉強しながら、
表を作り上げていきたいと思います。
助かりました。本当にありがとうございました。
No.2
- 回答日時:
以下のような数式で本年度の有休残日数および時間数を表示できます。
=INT(本年度有給休暇数-取得週休休暇日数-取得有休時間数/"7:45")&"日"&TEXT("7:45"-MOD(取得有休時間数,"7:45"),"h時間m分")
取得有休時間数が「2:30」のような形式で入力されていない場合は、たとえば「2.5」と入力しているなら上記の数式の時間の部分を24で割り算してください。
もし、それ以外の方式で入力している場合は、実際に使用している入力形式を具体的に提示してください。
No.1
- 回答日時:
時休部分のセルのデータの入れ方がちょっと不明ですが。
F4に開始日数(たとえば20)
D8以下に日単位の休暇取得数(たとえば1や2や3)
E8以下に時休みの取得数(たとえば 1:00 とか 6:00 とか,書式設定はh:mm)
として
F8以下に残数
=IF(D8+E8,$F$4-SUM($D$8:D8)-ROUNDUP(SUM($E$8:E8)/"7:45",0)+MOD("7:45"-SUM($E$8:E8),"7:45"),"")
G8以下に取得済数
=IF(D8+E8,SUM($D$8:D8)+INT(SUM($E$8:E8)/"7:45")+MOD(SUM($E$8:E8),"7:45"),"")
FG列はセルの表示形式を d日h時間m分 と設定しておく。
#Ver2
D8以下に,
日休を取得した場合は1,2,3を記入する
時間休を取得した場合は1:00や4:45を記入する
7:45以上の時間数を記入してはいけない
日休と時間休の併用(1日と4:00など)は認めない
E列は使わない
F8:
=IF(D8,$F$4-SUMIF($D$8:D8,">=1")-ROUNDUP(SUMIF($D$8:D8,"<1")/"7:45",0)+MOD("7:45"-SUMIF($D$8:D8,"<1"),"7:45"),"")
G8:
=IF(D8,SUMIF($D$8:D8,">=1")+INT(SUMIF($D$8:D8,"<1")/"7:45")+MOD(SUMIF($D$8:D8,"<1"),"7:45"),"")
この回答への補足
すみません、もう1つ質問です。
教えていただいた数式についての質問なのですが、
#ver1の数式を入力した状態で、
F4が20であれば問題ないのですが、
F4を40にして、時間休に「1:00」と入力すると、
残りの日時数が8日6時間45分となってしまいます。
どういうことなのでしょうか。
すみませんが、今一度お願いします。
早速のご回答ありがとうございました。
今までは日数や時間数はセルが別々だったので、
どちらも整数で入力していました。(1日も1時間も「1」と。)
これを1:00と入力するということですね。
それでも全く問題はありません。
今数式を入れて確認してみました。
有給の残りの部分のみで確認しましたが、
イメージ通りに表示されて感激しています。
他の数式も入力してみます。
本当にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
都道府県穴埋めゲーム
都道府県の名前を1人1つずつ投稿してください。全ての都道府県が出たら締め切ります!
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセルで年休を管理 (一日8時間単位で)
Excel(エクセル)
-
エクセルで「500時間」を「何日と何時間」で表示したい
その他(パソコン・スマホ・電化製品)
-
エクセルでの計算式もしくはシートの作り方 1日を7時間45分に
財務・会計・経理
-
-
4
エクセルの使い方 1日=7時間45分 計算方法
Excel(エクセル)
-
5
年次休暇の取得とのこりの自動計算について
Excel(エクセル)
-
6
excelで年休管理をするには
Excel(エクセル)
-
7
0.8=1あるいは0.8進法
Word(ワード)
-
8
7時間15分を1日と換算する計算について
Excel(エクセル)
-
9
お世話になっております エクセルで有給の管理をしたいのですが 数式がわかりません。ご教示お願いします
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの質問です。 F列からL...
-
作成した数式を値として表示し...
-
エクセルの文字が途中から消える
-
条件付き書式設定で罫線を引き...
-
Excel関数について教えてくださ...
-
Excelの警告について
-
Excel関数について教えてくださ...
-
ワークシートに出現したこの画...
-
エクセルのセル内に分数などの...
-
タイムスタンプとテキストから...
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
エクセルの数式バーのフォント...
-
エクセルの「条件付き書式」を...
-
エクセル日付 文字列の関数がエ...
-
Excelでの文字色
-
Excelの数字の前に入っている空...
-
Excelについて教えてください。...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報