Finding a Issue when I calculate shift .

R

ragavajothee

New Member
Joined
May 24, 2011
Messages
16
Hello ,

Need your help ...

When I'm trying to calculate the shift from the time downloaded from my ERP [ Texware ] as said below ..

TimeBreaks TakenShiftDisc & ConclusionDateTimeWhseNotes/Rmks
08:36:57Night
0​
15-11-2022​
08:36:57
7​
INSP 1
08:42:16
00:05:19​
Night
0​
15-11-2022​
08:42:16
7​
INSP 1
08:42:23
00:00:07​
Night
0​
15-11-2022​
08:42:23
7​
INSP 1
08:49:11
00:06:48​
Night
0​
15-11-2022​
08:49:11
7​
INSP 1
08:49:15
00:00:04​
Night
0​
15-11-2022​
08:49:15
7​
INSP 1
08:50:49
00:01:34​
Night
0​
15-11-2022​
08:50:49
7​
INSP 1
08:57:43
00:06:54​
Night
0​
15-11-2022​
08:57:43
7​
INSP 1
08:57:53
00:00:10​
Night
0​
15-11-2022​
08:57:53
7​
INSP 1
09:03:41
00:05:48​
Night
0​
15-11-2022​
09:03:41
7​
INSP 1
09:05:26
00:01:45​
Night
0​
15-11-2022​
09:05:26
7​
INSP 1
09:06:02
00:00:36​
Night
0​
15-11-2022​
09:06:02
7​
INSP 1
09:08:54
00:02:52​
Night
0​
15-11-2022​
09:08:54
7​
INSP 1
09:10:05
00:01:11​
Night
0​
15-11-2022​
09:10:05
7​
INSP 1
09:15:04
00:04:59​
Night
0​
15-11-2022​
09:15:04
7​
INSP 1
09:16:27
00:01:23​
Night
0​
15-11-2022​
09:16:27
7​
INSP 1
09:24:03
00:07:36​
Night
0​
15-11-2022​
09:24:03
7​
INSP 1

The formula is is working properly . I do not know the reason why . Can I request to solve this issue . Thanks .
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
in your previous post - I posted a solution

using

=IF(OR(A2>23/24,A2<=7/24),"3rd Shift",IF(A2<=15/24,"1st Shift","2nd Shift"))

i cannot see in the image above - what formula you are using - or where the issue is

Is the time actually a real time recognised by Excel or is it TEXT ......

If its text then it maybe you need to use the import wizard.

you last post said
Day shift or 1'st shift 7.01 AM to 15.00 PM
Half night 2'nd shift 15.01 PM to 23.00 PM
Half night 3'rd shift 23.01 PM to 7.00 PM

All those times are between 7 and 15
and i have applied the formula

and changed the time in 2 rows - otherwise all just 1 shift

Book50
ABCDEFGHIJKLMNOPQRST
1TimeBreaks TakenShiftDisc & ConclusionDateTimeWhseNotes/Rmks
28:36:57Night015-11-20228:36:577INSP 11st ShiftFALSEIndian TimeShift
38:42:1600:05:19Night015-11-20228:42:167INSP 11st ShiftFALSE00:00:01Day shiftor1'st shift7.01 AM to 15.00 PM
48:42:2300:00:07Night015-11-20228:42:237INSP 11st ShiftFALSE01:00:01Half night2'nd shift15.01 PM to 23.00 PM
58:49:1100:06:48Night015-11-20228:49:117INSP 11st ShiftFALSE02:00:01Half night3'rd shift23.01 PM to 7.00 PM
68:49:1500:00:04Night015-11-20228:49:157INSP 11st ShiftFALSE
78:50:4900:01:34Night015-11-20228:50:497INSP 11st ShiftFALSE
88:57:4300:06:54Night015-11-20228:57:437INSP 11st ShiftFALSE
98:57:5300:00:10Night015-11-20228:57:537INSP 11st ShiftFALSE
109:03:4100:05:48Night015-11-20229:03:417INSP 11st ShiftFALSE
119:05:2600:01:45Night015-11-20229:05:267INSP 11st ShiftFALSE
129:06:0200:00:36Night015-11-20229:06:027INSP 11st ShiftFALSE
136:00:0000:02:52Night015-11-20229:08:547INSP 13rd ShiftTRUE
149:10:0500:01:11Night015-11-20229:10:057INSP 11st ShiftFALSE
1521:00:0000:04:59Night015-11-20229:15:047INSP 12nd ShiftFALSE
169:16:2700:01:23Night015-11-20229:16:277INSP 11st ShiftFALSE
179:24:0300:07:36Night015-11-20229:24:037INSP 11st ShiftFALSE
18
Sheet2
Cell Formulas
RangeFormula
J2:J17J2=IF(OR(A2>23/24,A2<=7/24),"3rd Shift",IF(A2<=15/24,"1st Shift","2nd Shift"))
K2:K17K2=OR(A2>23/24,A2<=7/24)




The Previous sample

Book50
AB
1Indian TimeShift
200:00:013rd Shift
301:00:013rd Shift
402:00:013rd Shift
503:00:013rd Shift
604:00:013rd Shift
705:00:013rd Shift
806:00:013rd Shift
907:00:011st Shift
1008:00:011st Shift
1109:00:011st Shift
1210:00:011st Shift
1311:00:011st Shift
1412:00:011st Shift
1513:00:011st Shift
1614:00:011st Shift
1715:00:012nd Shift
1816:00:012nd Shift
1917:00:012nd Shift
2018:00:012nd Shift
2119:00:012nd Shift
2220:00:012nd Shift
2321:00:012nd Shift
2422:00:012nd Shift
2523:00:013rd Shift
2600:00:013rd Shift
2701:00:013rd Shift
2802:00:013rd Shift
2903:00:013rd Shift
3004:00:013rd Shift
Sheet1
Cell Formulas
RangeFormula
B2:B30B2=IF(OR(A2>23/24,A2<=7/24),"3rd Shift",IF(A2<=15/24,"1st Shift","2nd Shift"))
A3:A30A3=A2+TIMEVALUE("1:00:00")



Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
 
Last edited:
Upvote 0
etaf said:
in your previous post - I posted a solution

using

=IF(OR(A2>23/24,A2<=7/24),"3rd Shift",IF(A2<=15/24,"1st Shift","2nd Shift"))

i cannot see in the image above - what formula you are using - or where the issue is

Is the time actually a real time recognised by Excel or is it TEXT ......

If its text then it maybe you need to use the import wizard.

you last post said
Day shift or 1'st shift 7.01 AM to 15.00 PM
Half night 2'nd shift 15.01 PM to 23.00 PM
Half night 3'rd shift 23.01 PM to 7.00 PM

All those times are between 7 and 15
and i have applied the formula

and changed the time in 2 rows - otherwise all just 1 shift

Book50
ABCDEFGHIJKLMNOPQRST
1TimeBreaks TakenShiftDisc & ConclusionDateTimeWhseNotes/Rmks
28:36:57Night015-11-20228:36:577INSP 11st ShiftFALSEIndian TimeShift
38:42:1600:05:19Night015-11-20228:42:167INSP 11st ShiftFALSE00:00:01Day shiftor1'st shift7.01 AM to 15.00 PM
48:42:2300:00:07Night015-11-20228:42:237INSP 11st ShiftFALSE01:00:01Half night2'nd shift15.01 PM to 23.00 PM
58:49:1100:06:48Night015-11-20228:49:117INSP 11st ShiftFALSE02:00:01Half night3'rd shift23.01 PM to 7.00 PM
68:49:1500:00:04Night015-11-20228:49:157INSP 11st ShiftFALSE
78:50:4900:01:34Night015-11-20228:50:497INSP 11st ShiftFALSE
88:57:4300:06:54Night015-11-20228:57:437INSP 11st ShiftFALSE
98:57:5300:00:10Night015-11-20228:57:537INSP 11st ShiftFALSE
109:03:4100:05:48Night015-11-20229:03:417INSP 11st ShiftFALSE
119:05:2600:01:45Night015-11-20229:05:267INSP 11st ShiftFALSE
129:06:0200:00:36Night015-11-20229:06:027INSP 11st ShiftFALSE
136:00:0000:02:52Night015-11-20229:08:547INSP 13rd ShiftTRUE
149:10:0500:01:11Night015-11-20229:10:057INSP 11st ShiftFALSE
1521:00:0000:04:59Night015-11-20229:15:047INSP 12nd ShiftFALSE
169:16:2700:01:23Night015-11-20229:16:277INSP 11st ShiftFALSE
179:24:0300:07:36Night015-11-20229:24:037INSP 11st ShiftFALSE
18
Sheet2
Cell Formulas
RangeFormula
J2:J17J2=IF(OR(A2>23/24,A2<=7/24),"3rd Shift",IF(A2<=15/24,"1st Shift","2nd Shift"))
K2:K17K2=OR(A2>23/24,A2<=7/24)




The Previous sample

Book50
AB
1Indian TimeShift
200:00:013rd Shift
301:00:013rd Shift
402:00:013rd Shift
503:00:013rd Shift
604:00:013rd Shift
705:00:013rd Shift
806:00:013rd Shift
907:00:011st Shift
1008:00:011st Shift
1109:00:011st Shift
1210:00:011st Shift
1311:00:011st Shift
1412:00:011st Shift
1513:00:011st Shift
1614:00:011st Shift
1715:00:012nd Shift
1816:00:012nd Shift
1917:00:012nd Shift
2018:00:012nd Shift
2119:00:012nd Shift
2220:00:012nd Shift
2321:00:012nd Shift
2422:00:012nd Shift
2523:00:013rd Shift
2600:00:013rd Shift
2701:00:013rd Shift
2802:00:013rd Shift
2903:00:013rd Shift
3004:00:013rd Shift
Sheet1
Cell Formulas
RangeFormula
B2:B30B2=IF(OR(A2>23/24,A2<=7/24),"3rd Shift",IF(A2<=15/24,"1st Shift","2nd Shift"))
A3:A30A3=A2+TIMEVALUE("1:00:00")



Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
Click to expand...
I just copied your formula and made the changes as said below ..

1'st shift to " Day shift"
2'nd shift to " Half Night"
3'rd shift to " Night Shift"

It is to be noted that from our ERP , everyday , we copy data and put it in excel files from where I copied this rows and columns to you in my last message . Couple of days back , when I do copy the data from ERP to excel file , it is not working properly .
Day shift showing as " night shift and other shirts vice versa .

Pls help me out as how to correct this or repair this . Thanks .

Best Regards
Raj
 
Upvote 0
you can see the times in the example i gave

in the formula I may have named them incorrectly
Day shift or 1'st shift 7.01 AM to 15.00 PM
Half night 2'nd shift 15.01 PM to 23.00 PM
Half night 3'rd shift 23.01 PM to 7.00 PM

sorry about that

=IF(OR(A2>23/24,A2<=7/24),"3rd Shift",IF(A2<=15/24,"1st Shift","2nd Shift"))

changed the names

the first section OR(A2>23/24,A2<=7/24) checks if the time is above 23:00 and below 7am - and named "Half Night 3rd"
Then the next test is (A2<=15/24, - so less than 11pm - BUT we checked before is it was less than or equal too 7am - so now we are testing greater than 7am and less or equal to 3pm and calling that "Day Shift"
The anything else is ,"Half Night 2nd" , which means it must be greater then 3pm but less than 11pm

=IF(OR(A2>23/24,A2<=7/24),"Half Night 3rd",IF(A2<=15/24,"Day Shift","Half Night 2nd"))

see the 2 tables below - is that whats needed - or am i still missing something

Book50
ABCDEFGHIJ
1TimeBreaks TakenShiftDisc & ConclusionDateTimeWhseNotes/Rmks
28:36:57Night015-11-20228:36:577INSP 1Day Shift
38:42:1600:05:19Night015-11-20228:42:167INSP 1Day Shift
48:42:2300:00:07Night015-11-20228:42:237INSP 1Day Shift
58:49:1100:06:48Night015-11-20228:49:117INSP 1Day Shift
68:49:1500:00:04Night015-11-20228:49:157INSP 1Day Shift
78:50:4900:01:34Night015-11-20228:50:497INSP 1Day Shift
88:57:4300:06:54Night015-11-20228:57:437INSP 1Day Shift
98:57:5300:00:10Night015-11-20228:57:537INSP 1Day Shift
109:03:4100:05:48Night015-11-20229:03:417INSP 1Day Shift
119:05:2600:01:45Night015-11-20229:05:267INSP 1Day Shift
129:06:0200:00:36Night015-11-20229:06:027INSP 1Day Shift
136:00:0000:02:52Night015-11-20229:08:547INSP 1Half Night 3rd
149:10:0500:01:11Night015-11-20229:10:057INSP 1Day Shift
1521:00:0000:04:59Night015-11-20229:15:047INSP 1Half Night 2nd
169:16:2700:01:23Night015-11-20229:16:277INSP 1Day Shift
179:24:0300:07:36Night015-11-20229:24:037INSP 1Day Shift
Sheet2
Cell Formulas
RangeFormula
J2:J17J2=IF(OR(A2>23/24,A2<=7/24),"Half Night 3rd",IF(A2<=15/24,"Day Shift","Half Night 2nd"))


or for each hour
Book50
ABCDEFGHIJ
1Indian TimeShift
200:00:01Half Night 3rd ShiftDay shiftor1'st shift7.01 AM to 15.00 PM
301:00:01Half Night 3rd ShiftHalf night2'nd shift15.01 PM to 23.00 PM
402:00:01Half Night 3rd ShiftHalf night3'rd shift23.01 PM to 7.00 PM
503:00:01Half Night 3rd Shift
604:00:01Half Night 3rd Shift
705:00:01Half Night 3rd Shift160
806:00:01Half Night 3rd Shift
907:00:01Day Shift
1008:00:01Day Shift
1109:00:01Day Shift
1210:00:01Day Shift
1311:00:01Day Shift
1412:00:01Day Shift
1513:00:01Day Shift
1614:00:01Day Shift
1715:00:01Half Night 2nd Shift
1816:00:01Half Night 2nd Shift
1917:00:01Half Night 2nd Shift
2018:00:01Half Night 2nd Shift
2119:00:01Half Night 2nd Shift
2220:00:01Half Night 2nd Shift
2321:00:01Half Night 2nd Shift
2422:00:01Half Night 2nd Shift
2523:00:01Half Night 3rd Shift
2600:00:01Half Night 3rd Shift
2701:00:01Half Night 3rd Shift
2802:00:01Half Night 3rd Shift
2903:00:01Half Night 3rd Shift
3004:00:01Half Night 3rd Shift
31
Sheet1
Cell Formulas
RangeFormula
B2:B30B2=IF(OR(A2>23/24,A2<=7/24),"Half Night 3rd Shift",IF(A2<=15/24,"Day Shift","Half Night 2nd Shift"))
A3:A30A3=A2+TIMEVALUE("1:00:00")
F7F7=8*20
 
Upvote 0
Hi etaf ,

Thanks for your inputs . I completely got your point . Once again I checked with my file and found some formatting mistake which I'm not able to crack it ...I'm just trying to show it to you as shown below ..
DateB.TimeShift
15-11-2022​
08:36:46​
1st Shift
15-11-2022​
08:36:52​
1st Shift
15-11-2022​
08:36:57​
1st Shift
15-11-2022​
08:42:163rd Shift

In the first three rows what I have done is .. the moment , I downloaded the datum from ERP , I double clicked and got the formula correct . If you look at the 4'th row which I have not double clicked and the formula going wrong . Can you pls advise what suppose to be done in this . Thanks .
 
Upvote 0
the time is left justified - which means its not a time - its just text
which maybe the way the downloaded program opens in excel

if you format the cell - as general , it should show as a decimal number
0.362685185185185
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
122
Fluff
Fluff
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
339
awoohaw
awoohaw
C
  • Question
Incorrect date
2 3
Replies
25
Views
556
awoohaw
awoohaw
M
  • Question
Complexed Time Difference Formula
2
Replies
17
Views
1K
mahmed1
M
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
460
felixstraube
F

Forum statistics

Threads
1,215,831
Messages
6,127,138
Members
449,361
Latest member
VBquery757

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