# Formula for calculating a shift from a time of day

#### SteveG

##### New Member
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?

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

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

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

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.

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?

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.

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......

Replies
2
Views
115
Replies
4
Views
192
Replies
0
Views
117
Replies
7
Views
206
Replies
79
Views
4K

Threads
1,219,570
Messages
6,149,045
Members
450,853
Latest member
xtiinctt

### 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

### 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