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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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?
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
 

KniteMare

Board Regular
Joined
Mar 4, 2002
Messages
238
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
 

SteveG

New Member
Joined
Aug 1, 2002
Messages
36

ADVERTISEMENT

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 Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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.
 

SteveG

New Member
Joined
Aug 1, 2002
Messages
36

ADVERTISEMENT

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?
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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.
 

KniteMare

Board Regular
Joined
Mar 4, 2002
Messages
238
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......
 

Forum statistics

Threads
1,144,363
Messages
5,723,917
Members
422,527
Latest member
JayTheKaz

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
Top