Formula for calculating a shift from a time of day

SteveG

New Member
Joined
Aug 1, 2002
Messages
36
Is there a way that if I have a timestamp of 12:33pm I can change it with a formula to a shift?
1st Shift= 0800-1600
2nd Shift= 1600-2400
3rd Shift= 0000-0800

Thanks, Steve
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
On 2002-09-05 10:24, SteveG wrote:
Is there a way that if I have a timestamp of 12:33pm I can change it with a formula to a shift?
1st Shift= 0800-1600
2nd Shift= 1600-2400
3rd Shift= 0000-0800

Thanks, Steve

Which shift would 12:33pm be?
also, when you say timestamp, is this generally using some kind of Workforce Management application?
 
Upvote 0
Not sure if this what you're after, but with your time stamp in A1 you could try
=VLOOKUP(A1,{0,"3rd Shift";0.333333333333333,"1st Shift";0.666666666666667,"2nd Shift"},2)

Or if A1 is not a number to excel, change the first part to
=vlookup(A1+0,...

good luck
 
Upvote 0
To get you started do this

=IF(AND(A1>800,A1<1600),"First",(IF(AND(A1>=1600,A1<2400),"Second","Third")))

Yours in EXCELent Frustration

KniteMare

Crime Pays.... Every other Thursday.
"The Department of Corrections"
This message was edited by KniteMare on 2002-09-05 11:09
 
Upvote 0
12:33pm would fall into 1st shift because it is between 8:00am and 4:00pm.

And when I said timestamp, I just meant that I have a column named TIME and it holds all the times of day that particular records were made. And I would like a formula that translates it to another column that I created for the shift data. Either shift 1, 2 or 3 depending on what time is in the TIME column for that record.
Thanks, Steve
 
Upvote 0
On 2002-09-05 11:28, SteveG wrote:
12:33pm would fall into 1st shift because it is between 8:00am and 4:00pm.

And when I said timestamp, I just meant that I have a column named TIME and it holds all the times of day that particular records were made. And I would like a formula that translates it to another column that I created for the shift data. Either shift 1, 2 or 3 depending on what time is in the TIME column for that record.
Thanks, Steve

Ian's formula should do the trick.
 
Upvote 0
I tried using you method KniteMare, But the only problem now is that the format that the time data comes in. (Example- 12:33PM)Because of the ":" it will not accept the formula. Is there any way around this?
 
Upvote 0
It sounds like the issue is that excel is not treating your time as a number. To coerce it into a time, you can use
=VLOOKUP((LEFT(A1,LEN(A1)-2)&" "&LEFT(RIGHT(A1,2)))+0,{0,"3rd Shift";0.3333,"1st Shift";0.6667,"2nd Shift"},2)

or if you perfer to use the if statement, replace A1 with
=((LEFT(A1,LEN(A1)-2)&" "&LEFT(RIGHT(A1,2)))+0

There may be better ways to get excel to read it as a time (subsitute?), but this should get you there.
 
Upvote 0
Alright I give UP, I suppose it can be done with formulas but this works and the users cannot delete it on you.!!
Just Code it in the Worksheet Change Event Like This.

'CODE STARTS

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then' Works anywhere in Column "A"
On Error GoTo 7' anything not in the correct format is ignored
Application.ScreenUpdating = False
Target.NumberFormat = "General"'Change it to general so that you can do the math
Select Case True'check which equation is True
Case Target.Value > 0.33333 And Target.Value < 0.66666 '= True
Target.Offset(0, 1).Value = "First"
Case Target.Value > 0.66666 And Target.Value < 0.9999 '= True
Target.Offset(0, 1).Value = "Second"
Case Else
Target.Offset(0, 1).Value = "Third"
End Select
Target.NumberFormat = "h:mm:ss AM/PM"'Change it back so the user does not know what just happened _
and you look real smart, and they give you a raise _
and you become rich and famous, well you get the idea.
Else:
End If
7
Application.ScreenUpdating = True
End Sub

'CODE ENDS

Yours in EXCELent Frustration

KniteMare

Mean while, back at the ranch......
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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