Board logo

標題: [發問] 如何將日期時間格式用函數求得時間+班別 [打印本頁]
作者: jaga0828    時間: 2014-12-29 20:29     標題: 如何將日期時間格式用函數求得時間+班別

本帖最後由 GBKEE 於 2014-12-30 06:10 編輯

版主我忘了選發問，所以重發了，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含以前都要以前一天為日期~
[attach]19967[/attach]
作者: HSIEN6001    時間: 2014-12-29 22:40

回復 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    時間: 2014-12-29 23:16

回復 1# jaga0828

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

提供我的計算方式，B和C欄分別拆解出日期與時間，再用E1和F1儲存格進行判斷，介於此區間為日班，答案為D欄的計算結果(除了第一列以外，其他答案都跟你一樣)
[attach]19969[/attach]
作者: p212    時間: 2014-12-30 10:40

本帖最後由 p212 於 2014-12-30 10:44 編輯

回復 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    時間: 2014-12-30 22:26

回復 2# HSIEN6001

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

回復 3# tku0216

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

我權限沒辦法下附件說~
但還是感謝你~
作者: jaga0828    時間: 2014-12-30 22:37

回復 4# p212

感謝大大幫忙~
貼上去無法顯示說~
作者: p212    時間: 2014-12-31 09:21

本帖最後由 p212 於 2014-12-31 09:32 編輯

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

回復 5# jaga0828
   
邏輯沒錯,剛剛試過沒問題
你再試一次!
[attach]19986[/attach]
作者: jaga0828    時間: 2014-12-31 22:25

本帖最後由 jaga0828 於 2014-12-31 22:28 編輯

回復 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 夜班

再麻煩大大幫忙一下~

[attach]19988[/attach]
作者: HSIEN6001    時間: 2015-1-1 10:21

本帖最後由 HSIEN6001 於 2015-1-1 10:24 編輯

回復 10# 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:30:01") 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

[attach]19991[/attach]
作者: jaga0828    時間: 2015-1-1 11:28

回復 11# HSIEN6001

感謝大大~目前運算都ok了~祝新年快樂~



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)