返回列表 上一主題 發帖

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

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

本帖最後由 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含以前都要以前一天為日期~
1229.jpg
2014-12-29 20:28

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

TOP

回復 1# jaga0828

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

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

TOP

本帖最後由 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(班別,"夜班","日班")),"")
向下複製公式
請參考!
fig.jpg

TOP

回復 2# HSIEN6001

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

TOP

回復 3# tku0216

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

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

TOP

回復 4# p212

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

TOP

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

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

TOP

回復 5# jaga0828
   
邏輯沒錯,剛剛試過沒問題
你再試一次!
日夜班.png
2014-12-31 17:42

TOP

本帖最後由 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 夜班

再麻煩大大幫忙一下~

20141231.jpg
2014-12-31 22:20

TOP

        靜思自在 : 君子如水,隨方就圓,無處不自在。
返回列表 上一主題