[發問] 如何將日期時間格式用函數求得時間+班別

jaga0828

版主我忘了選發問，所以重發了，SORRY

請問一下高手們~
假設我download的格式為=>  2014/11/13  05:59:00 PM  or 2014/11/13 17:59
  (前面日期都以起始時間為日期，假設2014/11/13 01:59  就得到11/12 夜班)
                                      
日班區分時間為=> 07:31~19:30
夜班區分時間為=>19:31~07:30
已知A欄                                        求B欄
2014/11/13 01:11 AM  =>11/12 夜班
2014/11/13 19:30 PM  =>11/13 日班
2014/11/15 00:15 AM => 11/14 夜班

前面日期都以起始時間為日期,前面日期都以起始時間為日期,前面日期都以起始時間為日期,過隔天清晨07:30含以前都要以前一天為日期~

下載 (12.72 KB)

2014-12-29 20:28

HSIEN6001

回復 1# jaga0828

試試!
    Sub XL()
    Dim E As Range
    With Sheets("Sheet1")   'Sheet1 工作表名稱
            For Each E In .Range("A1", [A65536].End(xlUp))
                If TimeValue(E) > TimeValue("00:00:01") And TimeValue(E) < TimeValue("07:30:00") Then
                   E.Offset(, 1) = (Format(DateValue(E) - 1, "mm/dd")) & " 夜班"
                ElseIf TimeValue(E) > TimeValue("07:31:00") And TimeValue(E) < TimeValue("19:30:00") Then
                   E.Offset(, 1) = (Format(E, "mm/dd")) & " 日班"
                Else
                   E.Offset(, 1) = (Format(E, "mm/dd")) & " 夜班"
                End If
            Next
        End With
End Sub

tku0216

回復 1# jaga0828

請問一下你最後一個表格第一列是否計算錯誤？因為依照你的規則，我的答案是
2014/11/12 20:57 →11/11夜班

提供我的計算方式，B和C欄分別拆解出日期與時間，再用E1和F1儲存格進行判斷，介於此區間為日班，答案為D欄的計算結果(除了第一列以外，其他答案都跟你一樣)
計算.rar (7.02 KB)

下載次數: 4

2014-12-29 23:16

p212

回復 1# jaga0828
提供另一種函數解法
1、定義名稱「班別」，「參照到」輸入
=SUMPRODUCT((VALUE(TEXT(工作表1!$A2,"hh:mm"))>VALUE("7:30"))*(VALUE(TEXT(工作表1!$A2,"hh:mm"))<VALUE("19:31")))+1
2、儲存格B2輸入公式
=IFERROR(IF(VALUE(TEXT(A2,"hh:mm"))>VALUE("7:30"),TEXT(LEFT(A2,8),"mm/dd")&CHOOSE(班別,"夜班","日班"),TEXT(DATEVALUE(TEXT(LEFT(A2,8),"mm/dd"))-1,"mm/dd")&CHOOSE(班別,"夜班","日班")),"")
向下複製公式
請參考！

jaga0828

回復 2# HSIEN6001

首先感謝大大的解說~
但我發現有個問題，就是19:31~00:00 這區間沒有設到條件，導致2014/12/29 21:03:00   應該要顯示12/29 夜班 反而沒有跑出來，呈現空白格式
再麻煩大大幫忙想一下辦法，感恩!

jaga0828

回復 3# tku0216

先感謝大大的解答~
但是我範例提到是說過晚上12點到隔天07:30前都是用前一天日期，也就是說我的應該沒有錯~
2014/11/12 20:42=>11/12 夜班
2014/11/13 00:25=>11/12 夜班

我權限沒辦法下附件說~
但還是感謝你~

jaga0828

回復 4# p212

感謝大大幫忙~
貼上去無法顯示說~

p212

回復 7# jaga0828
請問您有定義名稱？否則無法執行儲存格B2的公式。
如4#之圖示（註：圖示範例有欄位名稱，若不需欄位名稱，則請自行修改「定義名稱」的「參照到」公式與儲存格B2的公式中對應儲存格，如A2改成A1）
1、游標先停於儲存格B2後再進行「定義名稱」，貼上「參照到」的公式。
2、接著儲存格B2貼上公式

HSIEN6001

回復 5# jaga0828
   
邏輯沒錯,剛剛試過沒問題
你再試一次!

下載 (42.27 KB)

2014-12-31 17:42

jaga0828

回復 9# HSIEN6001


大大祝新年快樂! 不好意思，我有在Run 一次，但遇到如附圖的異常點

我之前的需求為
日班區分時間為=> 07:31~19:30   顯示日期都以起始使間為主，後面再加上班別
夜班區分時間為=>19:31~07:30    顯示日期都以起始使間為主，後面再加上班別

2014/12/29  07:30:00  =>12/28 夜班 但程式跑出來為12/29 夜班
2014/12/29  19:30:00 =>12/29 日班 但程式跑出來為12/29 日班
2014/12/29  07:31:00 =>12/29 日班 但程式跑出來為12/29 夜班

再麻煩大大幫忙一下~

下載 (33.82 KB)

2014-12-31 22:20