Date-Time in cell what shift is working

F

FROGGER24

Well-known Member
Joined
May 22, 2004
Messages
704
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
worksheet "Data" formatted like this. I need to look at the date and time in cell AD2, then go to worksheet "charts" to find the day, column B represents the day shift 05:00 - 16:59, column C represents night shift from 17:00 - 04:59. Column BO2 in the example would equal CC. Hopefully I explained clearly enough.
Book2.xlsm
ADBO
1NOR Tendered NOR Tendered -Chart
23/30/21 13:05
33/30/21 13:05
44/3/21 13:46
54/3/21 19:35
64/7/21 0:01
73/30/21 0:01
83/30/21 0:01
93/30/21 12:30
103/31/21 0:05
DATA


the shift will be on the next worksheet called Charts
Book2.xlsm
ABC
1DateDay ShiftNight Shift
703/10/2021BBDD
713/11/2021BBDD
723/12/2021BBDD
733/13/2021BBDD
743/14/2021CCAA
753/15/2021CCAA
763/16/2021CCAA
773/17/2021CCAA
783/18/2021DDBB
793/19/2021DDBB
803/20/2021DDBB
813/21/2021DDBB
CHARTS
Cell Formulas
RangeFormula
B70:B81B70=B54
C70:C81C70=IF(B70="AA","CC",IF(B70="BB","DD",IF(B70="CC","AA",IF(B70="DD","BB"))))
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Im not sure if I am misunderstanding but your example does not seem to add up, you mentioned BO2 should equal CC but I dont see how. You mention you look at the date and time in AD2 which is 03/30/21, looking at charts I dont see 03/30/21 listed? I see 03/14-03/17 with CC in Day Shift?
 
Upvote 0
I apologize, didn't copy down far enough in first screen shot. the column lengths will be variable as more data will be added monthly. If I need to split the datetime into seperate columns, I am ok with that. 12 hours shifts =Day shift starts 05:00 - 17:00, Night Shift is from 17:01 - 04:59
Desired results
Book2.xlsm
ADBO
1NOR Tendered NOR Tendered -Chart
23/30/21 13:05CC
33/30/21 13:05CC
44/3/21 13:46DD
54/3/21 19:35BB
64/7/21 0:01CC
73/30/21 0:01AA
83/30/21 0:01AA
93/30/21 12:30CC
103/31/21 0:05AA
113/31/21 0:05AA
123/31/21 13:00CC
133/31/21 15:00CC
143/31/21 17:49AA
153/31/21 18:34AA
DATA

Charts data:
Book2.xlsm
BPBQBR
4DateDay ShiftNight Shift
53/30/2021CCAA
63/31/2021CCAA
74/1/2021CCAA
84/2/2021CCAA
94/3/2021DDBB
104/4/2021DDBB
114/5/2021DDBB
124/6/2021DDBB
134/7/2021AACC
DATA
 
Last edited:
Upvote 0
VBA Code: 
Sub Test90()

Dim myRangeA As Long
Dim myRangeB As Long
Dim Shift As String
Dim i As Long
Dim e As Long
Dim myDate As Date
Dim myDate2 As Date


myRangeA = Sheets("Data").Cells(Rows.Count).End(xlToLeft).Row
myRangeB = Sheets("Charts").Cells(Rows.Count).End(xlToLeft).Row

For i = 2 To myRangeA

myDate = Sheets("Data").Range("AD" & i).Value

If Hour(myDate) >= 5 And Hour(myDate) <= 17 Then
    If Hour(myDate) = 17 And Minute(myDate) = 0 Then
        Shift = "Day"
    ElseIf Hour(myDate) <> 17 Then
        Shift = "Day"
    End If
End If

If Hour(myDate) <= 4 Or Hour(myDate) >= 17 Then
    If Hour(myDate) = 17 And Minute(myDate) > 0 Then
        Shift = "Night"
    ElseIf Hour(myDate) <> 17 Then
        Shift = "Night"
    End If
End If

For e = 2 To myRangeB

myDate2 = Sheets("Charts").Range("A" & e).Value

If Month(myDate2) = Month(myDate) And Day(myDate2) = Day(myDate) And Year(myDate2) = Year(myDate) Then
    
    If Shift = "Day" Then
        Sheets("Data").Range("BO" & i).Value = Sheets("Charts").Range("B" & e).Value
    ElseIf Shift = "Night" Then
        Sheets("Data").Range("BO" & i).Value = Sheets("Charts").Range("C" & e).Value
    End If
    Exit For
End If

Next e


Next i

End Sub
 
Upvote 0
Solution
Coding4fun, you nailed it, thank you for that piece of code.

Can I modify these line of codes, because I have 26 columns of data with about 5000 rows of data
myDate = Sheets("Data").Range("AD" & i).Value

Sheets("Data").Range("BO" & i).Value = Sheets("Charts").Range("B" & e).Value
Sheets("Data").Range("BO" & i).Value = Sheets("Charts").Range("C" & e).Value
 
Upvote 0
This assumes you mean you have multiple columns you want to check and populate in your Data sheet

2022-05-14 10_43_54-.png


VBA Code: 
Sub Test90()

Dim myRangeA As Long
Dim myRangeB As Long
Dim Shift As String
Dim i As Long
Dim e As Long
Dim Found As Long
Dim myDate As Date
Dim myDate2 As Date
Dim myDate3 As Date


myRangeA = Sheets("Data").Cells(Rows.Count).End(xlToLeft).Row
myRangeB = Sheets("Charts").Cells(Rows.Count).End(xlToLeft).Row

For i = 2 To myRangeA

myDate = Sheets("Data").Range("AD" & i).Value
myDate3 = Sheets("Data").Range("BP" & i).Value

If Hour(myDate) >= 5 And Hour(myDate) <= 17 Then
    If Hour(myDate) = 17 And Minute(myDate) = 0 Then
        Shift = "Day"
    ElseIf Hour(myDate) <> 17 Then
        Shift = "Day"
    End If
End If

If Hour(myDate) <= 4 Or Hour(myDate) >= 17 Then
    If Hour(myDate) = 17 And Minute(myDate) > 0 Then
        Shift = "Night"
    ElseIf Hour(myDate) <> 17 Then
        Shift = "Night"
    End If
End If

For e = 2 To myRangeB
Found = 0
myDate2 = Sheets("Charts").Range("A" & e).Value

If Month(myDate2) = Month(myDate) And Day(myDate2) = Day(myDate) And Year(myDate2) = Year(myDate) Then
    
    If Shift = "Day" Then
        Sheets("Data").Range("BO" & i).Value = Sheets("Charts").Range("B" & e).Value
    ElseIf Shift = "Night" Then
        Sheets("Data").Range("BO" & i).Value = Sheets("Charts").Range("C" & e).Value
    End If
    Found = Found + 1
End If

If Month(myDate2) = Month(myDate3) And Day(myDate2) = Day(myDate3) And Year(myDate2) = Year(myDate3) Then
    
    If Shift = "Day" Then
        Sheets("Data").Range("BQ" & i).Value = Sheets("Charts").Range("B" & e).Value
    ElseIf Shift = "Night" Then
        Sheets("Data").Range("BQ" & i).Value = Sheets("Charts").Range("C" & e).Value
    End If
    Found = Found + 1
End If

If Found = 2 Then Exit For

Next e


Next i

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
453
Sundance_Kid
S
S
  • Question Question
How to create a proper excel table from unorganized data?
Replies
5
Views
320
Burrgogi
Burrgogi
A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
263
alessiaexcels
A
A
  • Question Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
824
AquaBuoy
A
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
567
Alex Blakenburg
A

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top