Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
差分時間の計算方法
投稿日時: 23/12/18 13:46:18
投稿者: gorby

A1セルに  12/18/2023 11:11:11
A2セルに  12/18/2023 13:12:11
と記入されています。
A3セルにA2セルとA1セルの差分時間を計算する方法をご教示ください。DATEDIF関数ではダメでした。
 

回答
投稿日時: 23/12/18 16:05:30
投稿者: hatena
投稿者のウェブサイトに移動

01/01/2023 01:01:01 というように桁数が19桁固定だとして、
 
セルに下記の式を設定して、
=DATEVALUE(MID(A2,7,4)&"/"&LEFT(A2,5))+TIMEVALUE(RIGHT(A2,8))-DATEVALUE(MID(A1,7,4)&"/"&LEFT(A1,5))-TIMEVALUE(RIGHT(A1,8))
 
書式設定を ユーザー定義 で
[h]:mm:ss
と設定します。

回答
投稿日時: 23/12/18 16:24:05
投稿者: んなっと

そのまま =A2-A1 とすると #VALUE! エラーになるのであれば、
 
           A
1 12/31/2023 23:11:11
2  1/1/2024 07:12:11
3         8:01
 
A3
=SUMPRODUCT((MID(A1:A2,FIND(" ",A1:A2)-4,4)&"/"&REPLACE(A1:A2,FIND(" ",A1:A2)-5,5,))*{-1;1})
表示形式 [h]:mm:ss など

回答
投稿日時: 23/12/18 17:23:21
投稿者: WinArrow

A1,A2セルの表示形式が説明されていないので、
日付形式:mm/dd/yyyy hh:mm:ss
の場合の解決方法を紹介します。
作業用セルを使用します。
セルB1:=TEXT(A1,"[s]")/(24*60*60)
セルB2:=TEXT(A2,"[s]")/(24*60*60)
セルB3:=B2-B1
セルA3:=IF(INT(B3)>0,TEXT(INT(B3),"#日")&" "&TEXT(B3,"h:mm:ss"),IF(INT(B3)=0,TEXT(B3,"hh:mm:ss"),"ERROR"))
セルA3の表示形式は「標準」
 

回答
投稿日時: 23/12/19 00:30:44
投稿者: Mike

     A          B       C
1 12/18/2023 11:11:11 2023/12/18 11:11:11
2 12/18/2023 13:12:11 2023/12/18 13:12:11 2:01:00
 
 
B1: =RIGHT(LEFT(A1,FIND(" ",A1)-1),4)&"/"&LEFT(A1,LEN(SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),RIGHT(LEFT(A1,FIND(" ",A1)-1),4),""))-1)&MID(A1,FIND(" ",A1),9)
C2: =B2-B1 ←書式は [h]:mm:ss

投稿日時: 23/12/19 09:47:34
投稿者: gorby

んなっと さんの引用:
そのまま =A2-A1 とすると #VALUE! エラーになるのであれば、
 
           A
1 12/31/2023 23:11:11
2  1/1/2024 07:12:11
3         8:01
 
A3
=SUMPRODUCT((MID(A1:A2,FIND(" ",A1:A2)-4,4)&"/"&REPLACE(A1:A2,FIND(" ",A1:A2)-5,5,))*{-1;1})
表示形式 [h]:mm:ss など

 
やってみましたが、結果はA3セルに#N/Aが表示されます。原因分かりますでしょうか。
また、A1、A2セルは同じA列でたとえばA10, A18など離れてもよいですか?

回答
投稿日時: 23/12/19 10:07:18
投稿者: WinArrow

>やってみましたが、結果はA3セルに#N/Aが表示されます。原因分かりますでしょうか。
 
推測ですが、A1、A2セルの表示形式が「文字列」になっていないのではないでしょうか?
表示形式は、見た目だけではなく、「データ型」を兼ねているものがあります。
表示形式の中に「時刻」や「ユーザー定義」の中には、説明にあるような表示形式は存在しないので、
 
文字列として入力した(データ型は文字列)、
または、
ユーザー定義で「mm/dd/yyyy h:mm:ss」を定義した(データ型は時刻型に近い)
のどちらかでしょう。
この「文字列」と「時刻型」では、対応が全く違います。
まずは、表示形式を確認しましょう。

回答
投稿日時: 23/12/19 10:35:08
投稿者: WinArrow

追加レス
   
セルの入力方法で。データ型が異なる例を紹介しますう。
   
セルの表示形式:「標準」の場合
(1)「12/18/2023 11:11:11」
と入力すると「文字列」として認識されます。
(2)「2023/12/18 11:11:11」
と入力すると「時刻」として認識されます。
但し、「秒」部分は表示されませんが、「値」としては認識されています)。
(3)別ファイル(テキストファイルなど)から、テキスト形式を複写した場合。
 (3-1)データは(1)と同じ・・・・「文字列」として認識されます。
  (3-2)データは(2)と同じ・・・・「時刻」として認識されます。
    但し、「秒」部分は表示されませんが、「値」としては認識されています)。
  
※「見た目」だけで判断せずに、計算に使用する場合は、
 「データ型」を意識しましょう。
 特に日付、時刻については、注意が必要です。
 数式(含む関数)では、「値」が参照されます。(表示形式は参照されません。)

回答
投稿日時: 23/12/19 10:40:51
投稿者: WinArrow

私の(投稿日時: 23/12/18 17:23:21)のレスの
セルA3の数式について
IFS関数を使うと、もっとすっきりするかもしれません。

回答
投稿日時: 23/12/19 13:39:44
投稿者: んなっと

#N/A ではなくて #VALUE! エラーではないですか?
うまくいかないかもしれませんが、一応こんな方法もあります。
 
           A
1 12/31/2023 23:11:11
2  1/1/2024 07:12:11 
3       8:01:00
 
A3
=SUMPRODUCT((MID(A1:A2,FIND("/",A1:A2,4)+1,4)&"/"&REPLACE(A1:A2,FIND("/",A1:A2,4),6," "))*{-1;1})
 
※どこかのセルに
=SUMPRODUCT(AGGREGATE({14,15},6,1*CODE(MID(IF({1,0},A1,A2),ROW($1:$30),1)),1)*{1,0.01})
として、58.32ならおそらく正常。
63.1や8481.47などの場合、文字列に余計な文字が紛れ込んでいます。
 
 
※次の式はまだ試さないように。上の解決が先。
A10,A18の場合
=SUMPRODUCT((MID(IF({1;0},A10,A18),FIND("/",IF({1;0},A10,A18),4)+1,4)&"/"&REPLACE(IF({1;0},A10,A18),FIND("/",IF({1;0},A10,A18),4),6," "))*{-1;1})

回答
投稿日時: 23/12/19 13:43:02
投稿者: Suzu

gorby さんの引用:
やってみましたが、結果はA3セルに#N/Aが表示されます。原因分かりますでしょうか。

 
 
データ型の問題の場合には、基本 #VALUE! が表示される様です。
 
 
エラー値 #N/A を修正する方法
https://support.microsoft.com/ja-jp/office/%E3%82%A8%E3%83%A9%E3%83%BC%E5%80%A4-n-a-%E3%82%92%E4%BF%AE%E6%AD%A3%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-a9708411-f82e-4e1b-8a7e-28c28311b993
 
を見ると、
今回の数式
引用:
また、A1、A2セルは同じA列でたとえばA10, A18など離れてもよいですか?

を鑑みると
 
配列数式で、その配列数式を含む範囲とは行数または列数が異なる範囲が参照されている
 
が該当しているのではありませんか?
gorby さんが、参照範囲 を 編集していませんか?
 
まずは、例の様に、A1、A2 にそれぞれ値を入れ その上で A3 に数式を入れ確認してください。
 
 
引用:
また、A1、A2セルは同じA列でたとえばA10, A18など離れてもよいですか?

レスポンスのあった、んなっとさん の数式の場合、
配列を使用する SUMPRODUCT関数を使用していますので、
飛び飛びにて行うなら、{-1;1} の中身を変えれば可能でしょう。
 
A1 と A3 なら
=SUMPRODUCT((MID(A1:A3,FIND(" ",A1:A3)-4,4)&"/"&REPLACE(A1:A3,FIND(" ",A1:A3)-5,5,))*{-1;0;1})
 
A1 と A4 なら
=SUMPRODUCT((MID(A1:A4,FIND(" ",A1:A4)-4,4)&"/"&REPLACE(A1:A4,FIND(" ",A1:A4)-5,5,))*{-1;0;0;1})
の様になるかと。
 
 
それと、たくさんのか方が、回答をくださっています。
その内容について、どうなのでしょうか?
 
回答者も時間を割いて回答をしていますので、レスポンスは欲しいですよ。

回答
投稿日時: 23/12/19 20:40:55
投稿者: Mike

Suzu さんの引用:
回答者も時間を割いて回答をしていますので、レスポンスは欲しいですよ。
全く仰るとおりです。
投稿日時: 23/12/19 00:30:44 投稿者: Mike
も是非試してみてください。お願い→gorbyさん
m(_._)m

回答
投稿日時: 23/12/19 21:03:46
投稿者: WinArrow

A1,A2のセルの表示形式について、確認をお願いしているが、(返事がない)
 
ユーザー定義の「時刻」("mm/dd/yyyy h:mm:ss"」だとしたら
 
んなっと さん提案の数式

引用:

=SUMPRODUCT((MID(A1:A2,FIND(" ",A1:A2)-4,4)&"/"&REPLACE(A1:A2,FIND(" ",A1:A2)-5,5,))*{-1;1})

を、↓のように改造すれば、よいでしょう。
改造後の数式
=SUMPRODUCT((A1:A2)*{-1;1})
 
但し、A2<A1の関係にあるときは、エラー(###########)になります。

投稿日時: 23/12/21 09:47:39
投稿者: gorby

Suzu さんの引用:
gorby さんの引用:
やってみましたが、結果はA3セルに#N/Aが表示されます。原因分かりますでしょうか。

 
 
データ型の問題の場合には、基本 #VALUE! が表示される様です。
 
 
エラー値 #N/A を修正する方法
https://support.microsoft.com/ja-jp/office/%E3%82%A8%E3%83%A9%E3%83%BC%E5%80%A4-n-a-%E3%82%92%E4%BF%AE%E6%AD%A3%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-a9708411-f82e-4e1b-8a7e-28c28311b993
 
を見ると、
今回の数式
引用:
また、A1、A2セルは同じA列でたとえばA10, A18など離れてもよいですか?

を鑑みると
 
配列数式で、その配列数式を含む範囲とは行数または列数が異なる範囲が参照されている
 
が該当しているのではありませんか?
gorby さんが、参照範囲 を 編集していませんか?
 
まずは、例の様に、A1、A2 にそれぞれ値を入れ その上で A3 に数式を入れ確認してください。
 
 
引用:
また、A1、A2セルは同じA列でたとえばA10, A18など離れてもよいですか?

レスポンスのあった、んなっとさん の数式の場合、
配列を使用する SUMPRODUCT関数を使用していますので、
飛び飛びにて行うなら、{-1;1} の中身を変えれば可能でしょう。
 
A1 と A3 なら
=SUMPRODUCT((MID(A1:A3,FIND(" ",A1:A3)-4,4)&"/"&REPLACE(A1:A3,FIND(" ",A1:A3)-5,5,))*{-1;0;1})
 
A1 と A4 なら
=SUMPRODUCT((MID(A1:A4,FIND(" ",A1:A4)-4,4)&"/"&REPLACE(A1:A4,FIND(" ",A1:A4)-5,5,))*{-1;0;0;1})
の様になるかと。
 
 
それと、たくさんのか方が、回答をくださっています。
その内容について、どうなのでしょうか?
 
回答者も時間を割いて回答をしていますので、レスポンスは欲しいですよ。

 
申し訳ありませんが、現在別件で多忙です。みなさんのアドバイスを試す時間がありません。
回答は年明けまでお待ちください。

回答
投稿日時: 23/12/23 16:07:01
投稿者: Mike

良いお年をお迎えください。m(_._)m

投稿日時: 24/01/02 20:12:02
投稿者: gorby

gorby さんの引用:
Suzu さんの引用:
gorby さんの引用:
やってみましたが、結果はA3セルに#N/Aが表示されます。原因分かりますでしょうか。

 
 
データ型の問題の場合には、基本 #VALUE! が表示される様です。
 
 
エラー値 #N/A を修正する方法
https://support.microsoft.com/ja-jp/office/%E3%82%A8%E3%83%A9%E3%83%BC%E5%80%A4-n-a-%E3%82%92%E4%BF%AE%E6%AD%A3%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-a9708411-f82e-4e1b-8a7e-28c28311b993
 
を見ると、
今回の数式
引用:
また、A1、A2セルは同じA列でたとえばA10, A18など離れてもよいですか?

を鑑みると
 
配列数式で、その配列数式を含む範囲とは行数または列数が異なる範囲が参照されている
 
が該当しているのではありませんか?
gorby さんが、参照範囲 を 編集していませんか?
 
まずは、例の様に、A1、A2 にそれぞれ値を入れ その上で A3 に数式を入れ確認してください。
 
 
引用:
また、A1、A2セルは同じA列でたとえばA10, A18など離れてもよいですか?

レスポンスのあった、んなっとさん の数式の場合、
配列を使用する SUMPRODUCT関数を使用していますので、
飛び飛びにて行うなら、{-1;1} の中身を変えれば可能でしょう。
 
A1 と A3 なら
=SUMPRODUCT((MID(A1:A3,FIND(" ",A1:A3)-4,4)&"/"&REPLACE(A1:A3,FIND(" ",A1:A3)-5,5,))*{-1;0;1})
 
A1 と A4 なら
=SUMPRODUCT((MID(A1:A4,FIND(" ",A1:A4)-4,4)&"/"&REPLACE(A1:A4,FIND(" ",A1:A4)-5,5,))*{-1;0;0;1})
の様になるかと。
 
 
それと、たくさんのか方が、回答をくださっています。
その内容について、どうなのでしょうか?
 
回答者も時間を割いて回答をしていますので、レスポンスは欲しいですよ。

 
申し訳ありませんが、現在別件で多忙です。みなさんのアドバイスを試す時間がありません。
回答は年明けまでお待ちください。

 
本件RPAツールのUiPathで解決できましたので、意外に手間のかかるExcel関数ではやらないと思いますので解決済みとさせていただきます。