Excelの関数で可能でしょうか、日付や式の値の自動更新

よろしくお願いします。
VBAは使えないので、関数で何とか出来ないかと思っています。

月の残業可能時間（45時間）を超えないよう、
1　現在どれだけ残業しているか
2　今後どれだけ残業できるか（総時間）
3　今後の営業日数（自動更新したい）
4　今後の一日あたりの残業可能時間（2の数値を3で割った数字、　自動更新したい）


添付の画像では（見づらかったらすみません）、
本日5月5日
今後の営業日数は16日ですが、

これが5月6日になったら

本日は5月6日
今後の営業日数は15日
一日あたりの可能残業時間は新たな数値で割り算をした値となる

このような事は関数で可能でしょうか。

現状、手作業で、数値を変えたりしています。
自動でできたら格好いいなと思います。

お知恵を貸していただければと思います。

No.4 ベストアンサー 回答者： fujillin 回答日時： 2023/05/23 21:54

No1です。

＞EOMONTH(本日日付,0）
＞の本日日付の部分を自動更新するためには
＞本日日付の部分には、today()関数が入っているセルを参照する、
＞という理解でよろしいでしょうか。
試してみれば、わかる話ですが・・・
ご理解の通りです。
直接、TODAY関数を入れておいても同じことになります。

ただし、No1でお断りしたように、日付が自動更新されると、月替わりの際に「残り営業日数」なども自動更新されますので、シートのどこかで不都合が起きるかも知れません。
（全体像がよくわからないので、どうなのかはわかりませんけれども・・・）

この回答へのお礼

回答ありがとうございます。
月ごとにシートを分けて作成しているので、ご心配いただいて件はだ上部かと思います。

度々の回答ありがとうございました。

お礼日時：2023/05/26 16:24

No.3 回答者： goomania 回答日時： 2023/05/21 14:07

このご質問には回答を困難にしている理由が存在します。

それは、ご質問者の説明の中に、対象となる勤務先の休業日についての説明がないことです。

掲出された画像には5月1日～5月6日までの勤務状況が記録されていますが、普通に考えてゴールデンウィークに一日も休まないというのはどのような勤務体系なのか判りません。

また、
>本日5月5日今後の営業日数は16日ですが、これが5月6日になったら
>本日は5月6日今後の営業日数は15日･･･
と説明されており、土日祝日が休日の勤務先であれば、5月5日は祝日で今後の営業日数は18日であり、5月6日は土曜日ですから5月6日になっても、やはり今後の営業日数は18日のままのはずです。

土日祝日は休まずに営業し、その代わりに振替休日を与えるという場合であれば、5月3日～5月5日の3日間は祝日であるにもかかわらず出勤することになるので、この3日分を同月中に振替休日とするならば、18-3=15となり、営業日数の辻褄は合いますが、全く説明がないので判りません。

少なくとも土日が休業日では無いようです。そこで、仮に毎週水、木、金が休業日という場合の数式を作成してみました。

添付画像をご覧ください。シート内の画像のような位置に表があるものとします。
「本日の日付」を表示するD2セルには
=TODAY()
という数式を入れています。

「現在までの残業時間」を表示するD3セルには
=SUM(F12:AJ12)
という数式を入れています。1か月は最大31日なので31日分の範囲を用意し、それを集計するようにしました。

「残業可能時間」を表示するD4セルには
数式でなく手入力で数値を入れるものとします。

「本日以降可能残業時間」を表示するD5セルには
=D4-D3
という数式を入れています。

「営業可能日数」を表示するD6セルには
=NETWORKDAYS.INTL(D2,EOMONTH(D2,0),"0011100")
という数式を入れています。
No.1さんの回答にNETWORKDAYSという関数が登場します。土日祝が休業日の場合は、その関数で対応可能です。ただ、冒頭で述べたように、ご質問の勤務先の休業日は土日祝ではない気もしますので、NETWORKDAYS.INTL関数を用いました。"0011100"の部分は月曜～日曜に対応して休業曜日を1にすることで、休業日を除いて日数計算するための指定です。
ですから仮に月～水が休業日なら"1110000"という指定になります。

「一日あたり可能残業時間」を表示するD7セルには
=D5/D6
という数式を入れています。

さらに、毎日の勤務時間を記録するための日付としてF9セルに
=EOMONTH($D$2,-1)+COLUMN(A1)
という数式を入れて、AJ列まで右方向へコピーしています。

「勤務時間」を表示するF10～AJ10セルには
数式ではなく手入力で勤務時間を入力するものとします。

「標準勤務時間」を表示するF11セルには
=IF(F10="",0,"7:45"*1)
という数式を入れて、AJ列まで右方向へコピーしています。

「残業時間」を表示するF12セルには
=MAX(F10-F11,0)
という数式を入れて、AJ列まで右方向へコピーしています。

これで、残業可能時間や毎日の勤務時間など手入力部分を入力すれば、あとは自動計算で表示されます。
月が変われば自動的に営業可能日数等が新しい月のものになります。
ただ、最終勤務時間は翌日でないと確定しないように思います。
本日基準で作表すると、勤務時間確定入力を行わないうちに月替わりしてしまうという懸念もあります。この表の作成目的やチェックの実態に合わせて昨日基準で作成するなどの工夫が必要かも知れません。

勤務先の休業日が明確でないため、正確な回答は困難ですが、参考になればと思います。

No.2 回答者： angkor_h 回答日時： 2023/05/19 17:55

次のセルを用意してください。

本日の日付　　=today()
給与計算期末日　　手動入力、固定
残日数　　=給与計算期末日-本日の日付
　これが負数になれば、このシートは使えません(期限切れ)。

ご参考まで。

この回答へのお礼

回答ありがとうございます。

お礼日時：2023/05/26 16:25

No.1 回答者： fujillin 回答日時： 2023/05/19 17:46

こんにちは

＞3　今後の営業日数（自動更新したい）
が計算できれば良いものと解釈しました。

本日の日付は、TODAY()などで取得していると思いますので、シリアル値（エクセルの日付型の値）になっているものと仮定します。
「営業日」の計算ですので、特殊扱いとなる祝日や定休日を別にリストにしておく必要があります。

月末の日付は
　=EOMONTH(本日日付, 0)
で算出でき、
二つの日付の間の営業日数は
　=NETWORKDAYS(開始日, 終了日, 祝日リスト)
で計算できます。

これを組み合わせれば、
　　=NETWORKDAYS(本日日付, EOMONTH(本日日付, 0), 祝日リスト)
とすることで、月末までの営業日数を算出できます。
（「祝日リスト」は、上記の祝日リストのセル範囲です。省略すると土日だけを除いた日数になります。）

※ 完全自動計算の場合は、6/1に日付が変わった途端に、残り営業日数も増加しますのでご注意ください。
　（本日日付が手入力などであれば、その様なことは起きません。）
※ 詳細を知りたい場合は、個々の関数について調べてみればわかると思います。

この回答へのお礼

回答ありがとうございます。

EOMONTH(本日日付,0）
の本日日付の部分を自動更新するためには
本日日付の部分には、today()関数が入っているセルを参照する、
という理解でよろしいでしょうか。

もう少し教えていただければと思います。

お礼日時：2023/05/23 21:46