Hi Everyone,
I'm trying to calculate the shift worked between 3 shift
Day Shift 07:00-14:00
Afternoon shift 14:00- 23:00
Night Shift 23:00-07:00 (next day)
I have attached a simple excel sheet which has the correct hours manually entered. The cells highlighted orange was the two I has having issues with the following formula (not my work). (If start down 00:00- 07:00)
=IF(HOUR(B5)<=14,"Dayshift",IF(HOUR(B5)>=23,"Nightshift","Afternoon"))
I'm hoping you can help me.
Thanks,
Irfan
Last edited by nafriirfan; 09-15-2017 at 04:36 AM. Reason: wrong
Try
=LOOKUP(HOUR(B5),{0,7,14,23},{"Nightshift","Dayshift","Afternoon","Nightshift"})
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
OR TRY
=IF(HOUR(B5)<7,"Nightshift",IF(HOUR(B5)<=14,"Dayshift",IF(HOUR(B5)>=23,"Nightshift","Afternoon")))
wHAT IS THE ISSUE DEAR??
I THINK, IT'S WORKING PROPERLY.
YOU CAN CONVERT THE TIME INTO 24 HOUR FORMAT =TEXT(C12:C12,"[hh]:mm:ss"), AS YOU ENTERED THE TIME MANUALLY
Thanks so much . Its work. But if I want to change clock become
Day Shift 06:40-13:40
Afternoon shift 13:40 - 22:40
Night Shift 22:40-06:40 (next day)
What change clock in formula use minutes??
Thanks you soo much
Rgds
Irfan
Thanks so much . Its work. But if I want to change clock become
Day Shift 06:40-13:40
Afternoon shift 13:40 - 22:40
Night Shift 22:40-06:40 (next day)
What change clock in formula use minutes??
Thanks you soo much
Rgds
Irfan
Edit:
=LOOKUP(MOD(B5-1/36,1)*24,{0,6,13,22},{"Nightshift","Dayshift","Afternoon","Nightshift"})
@phuocam, @nflsales Thank you so much .Its work
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks