VBA code for adding in text based on another cell's time.

[bigbeat85]

I have the below table and in column labelled shift I want to be able to populate it with either "Day" or "Night" based on the column labelled Conducted on time only.

I still want to keep the date reference as this is used elsewhere.

For example If the time is between 6am and 6pm I want to label it as "Day". Between 6pm and 6am labelled as "Night".

I think I am down the right path with this if statement but it is not working for me.


Dim LastRow As Long
Dim i As Long
LastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow


If Range("C" & i).Value < DateValue("DD/MM/YYYY 06:00:00") And Range("C" & i).Value > DateValue("DD/MM/YYYY 18:00:00") Then
Range("D" & i).Value = "EVENING"
End If

Next i

SHIFT ON:		Title Page_Conducted on	SHIFT
Shift B		23/08/2023 11:58​
Shift C		22/08/2023 05:10​
Shift C		21/08/2023 05:18​
Shift A		19/08/2023 06:09​
Shift B		18/08/2023 18:49​
Shift A		18/08/2023 11:32​
Shift C		17/08/2023 17:09​
		17/08/2023 17:07​
Shift D		16/08/2023 05:07​
Shift A		15/08/2023 17:06​
Shift B		15/08/2023 05:33​
Shift B		14/08/2023 05:42​
		03/08/2023 11:05​
Shift B		27/07/2023 08:04​
Shift D		20/07/2023 05:05​
Shift D		19/07/2023 13:01​

 

[Bosquedeguate]

Why are you doing this with VBA code? Try the formula in the mini-sheet below.

DayNight.xlsx
	A	B	C	D
1	SHIFT ON:		Title Page_Conducted on	SHIFT
2	Shift B		23/08/2023 11:58	DAY
3	Shift C		22/08/2023 05:10	NIGHT
4	Shift C		21/08/2023 05:18	NIGHT
5	Shift A		19/08/2023 06:09	DAY
6	Shift B		18/08/2023 18:49	NIGHT
7	Shift A		18/08/2023 11:32	DAY
8	Shift C		17/08/2023 17:09	DAY
9			17/08/2023 17:07	DAY
10	Shift D		16/08/2023 05:07	NIGHT
11	Shift A		15/08/2023 17:06	DAY
12	Shift B		15/08/2023 05:33	NIGHT
13	Shift B		14/08/2023 05:42	NIGHT
14			03/08/2023 11:05	DAY
15	Shift B		27/07/2023 08:04	DAY
16	Shift D		20/07/2023 05:05	NIGHT
17	Shift D		19/07/2023 13:01	DAY
Sheet1

Cell Formulas
Range	Formula
D2:D17	D2	=IF(AND(TIMEVALUE(RIGHT(LEFT(C2,16),5)) > TIME(5,59,59),TIMEVALUE(RIGHT(LEFT(C2,16),5)) < TIME(18,0,1)),"DAY","NIGHT")

 

[bigbeat85]

I'm using vba as the csv file I use has different departments daily. So I need something to sort the information easily.

 

[Bosquedeguate]

I'm using vba as the csv file I use has different departments daily. So I need something to sort the information easily.

try this VBA code

Option Explicit
Public Sub DayNight()
  Dim LastRow As Long
  Dim i As Long
  Dim tmp
  Dim tv
  LastRow = Range("D" & Rows.Count).End(xlUp).Row
  For i = 2 To LastRow
    tmp = Split(Range("C" & i))
    If UBound(tmp) > 0 Then
      tv = TimeValue(Left(tmp(1), 5))
      Range("D" & i) = IIf(tv > TimeSerial(5, 59, 59) And tv < TimeSerial(18, 0, 1), "DAY", "NIGHT")
    End If
  Next i
End Sub

 

[bigbeat85]

It hasn't worked for me. The highlighted in bold should that be "If" not "IIF"?
There are no errors that pop up

If I do use If instead of iif it shows as a syntax error?

Dim LastRow As Long
Dim i As Long
Dim tmp
Dim tv
LastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
tmp = Split(Range("C" & i))
If UBound(tmp) > 0 Then
tv = TimeValue(Left(tmp(1), 5))
Range("D" & i) = IIf(tv > TimeSerial(5, 59, 59) And tv < TimeSerial(18, 0, 1), "DAY", "NIGHT")
End If
Next i

 

[rollis13]

Hi to all. I was working on something like this:

'[...]
For i = 2 To LastRow
    'If CDate(Mid(Range("C" & i).Value, 11, 6)) < TimeValue("06:00:00") Or CDate(Mid(Range("C" & i).Value, 11, 6)) > TimeValue("17:59:59") Then '(something wrong with the pasted data)
    If CDate(Mid(Range("C" & i).Value, 11, 5)) < TimeValue("06:00:00") Or CDate(Mid(Range("C" & i).Value, 11, 5)) > TimeValue("17:59:59") Then
        Range("D" & i).Value = "NIGHT"
    Else
        Range("D" & i).Value = "DAY"
    End If
Next i
'[...]

 

[Bosquedeguate]

Strange, what version of Excel are you using?
Here's the code with IF THEN ELSE statement

Public Sub DayNight()
  Dim LastRow As Long
  Dim i As Long
  Dim tmp
  Dim tv
  LastRow = Range("D" & Rows.Count).End(xlUp).Row
  For i = 2 To LastRow
    tmp = Split(Range("C" & i)) 'separate time from date
    If UBound(tmp) > 0 Then
      tmp = Split(tmp(1), ":") 'separate hours from minutes
      If UBound(tmp) > 0 Then
        tv = TimeSerial(Val(tmp(0)), Val(tmp(1)), 0)
        If tv > TimeSerial(5, 59, 59) And tv < TimeSerial(18, 0, 0) Then
          Range("D" & i) = "DAY"
        Else
          Range("D" & i) = "NIGHT"
        End If
      End If
    End If
  Next i
End Sub

 

[bigbeat85]

I'm using Excel 365. I found it odd too. Tried with the new code and still not working. Unless it is a setting in excel I am missing

 

[NoSparks]

The invisible character on the end of the values in column C bothered me so here's my go at this

Sub DayOrNight()
    Dim rng As Range, cel As Range
    Dim tv As Double
    
Set rng = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
rng.Replace what:=ChrW(8203), Replacement:="", LookAt:=xlPart

For Each cel In rng
    tv = TimeValue(cel)
    If tv >= 0.25 And tv < 0.75 Then
        cel.Offset(, 1) = "Day"
    Else
        cel.Offset(, 1) = "Night"
    End If
Next cel

End Sub

 

[rlv01]

Perhaps this.

Public Sub DayNight()
    Dim LastRow As Long
    Dim i As Long
    Dim DT As Date
    Dim TV1 As Date, TV2 As Date
    
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If VBA.IsDate(Range("C" & i).Value) Then
            DT = CDate(Range("C" & i).Value)
            DT = DT - Int(DT)
            TV1 = TimeValue("06:00:00")
            TV2 = TimeValue("18:00:00")
            
            If (DT > TV1) And (DT < TV2) Then
                Range("D" & i).Value = "DAY"
            Else
                Range("D" & i).Value = "NIGHT"
            End If
        Else
            Range("D" & i).Value = "not a valid time"
        End If