To capture desk utilization and shift utilization using Excel

[basubnb]

Desk Number Date Login Time Logout Time

N1L01 11/16/2012 10:18 11/16/2012 10:18 11/16/2012 22:18

N1L02 11/16/2012 9:18 11/16/2012 9:18 11/16/2012 21:18

N1M03 11/16/2012 8:18 11/16/2012 8:18 11/16/2012 20:18

N1M04 11/16/2012 10:18 11/16/2012 10:18 11/16/2012 22:18

N1R05 11/16/2012 11:18 11/16/2012 11:18 11/16/2012 23:18

N1R06 11/16/2012 10:18 11/16/2012 10:18 11/16/2012 22:18

N2L01 11/16/2012 6:28 11/16/2012 6:28 11/16/2012 18:28

N2L02 11/16/2012 8:18 11/16/2012 8:18 11/16/2012 20:18

N2M03 11/16/2012 10:18 11/16/2012 10:18 11/16/2012 22:18

N2M04 11/16/2012 7:18 11/16/2012 7:18 11/16/2012 19:18

N2R05 11/16/2012 10:18 11/16/2012 10:18 11/16/2012 22:18

N2R06 11/16/2012 8:18 11/16/2012 8:18 11/16/2012 20:18

N1L01 11/17/2012 10:18 11/17/2012 10:18 11/17/2012 22:18

N1L02 11/17/2012 9:18 11/17/2012 9:18 11/17/2012 21:18

N1M03 11/17/2012 8:18 11/17/2012 8:18 11/17/2012 20:18

N1M04 11/17/2012 10:18 11/17/2012 10:18 11/17/2012 22:18

N1R05 11/17/2012 11:18 11/17/2012 11:18 11/17/2012 23:18

1R06 11/17/2012 10:18 11/17/2012 10:18 11/17/2012 22:18

N2L01 11/17/2012 6:28 11/17/2012 6:28 11/17/2012 18:28

N2L02 11/17/2012 8:18 11/17/2012 8:18 11/17/2012 20:18

N2M03 11/17/2012 10:18 11/17/2012 10:18 11/17/2012 22:18

N2M04 11/17/2012 7:18 11/17/2012 7:18 11/17/2012 19:18

N2R05 11/17/2012 10:18 11/17/2012 10:18 11/17/2012 22:18

N2R06 11/17/2012 8:18 11/17/2012 8:18 11/17/2012 20:18


BASE DATA


Case


Column A to E is the base data for Shift detail and timing

Aim - To capture desk utilization and shift utilization

Column G to BC is the output required

Need to capture desk utilization in a haly hourly time details from base data automatically

the capture should be in zeros and one… (zero indicate the desk is vacant where as 1 indicate the desk is occupied)


I tried my best using Countifs() but not successful.


OUTPUT

Half Hourly time interval

Desk Number 0:00 0:30 1:00 1:30 2:00 2:30 3:00 3:30

N1L01 1 1 1

N1L02 0 0 0 0 1 1 1

N1M03

N1M04

N1R05

N1R06

N2L01

N2L02

N2M03

N2M04

 

[SirJB7]

Hi, basubnb!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Despite of this, I have a few doubts/questions about your requirement:

a) data set: you say columns A:E, 5 columns, but you have either 7 or 4 (if you consider date and time in a single cell)

b) data duplication: the three groups of dates, column 2-4-6 are identical, and the first two groups of hours 3-5 are identical too, and the third group of hours -7- is the same as previous plus 12 hours or expressed in 24h format

c) output: you say columns G:BC so there are 49 columns, but taking the 24 hours a day at half and hour intervals that's 28


So, would you please be more careful and accurate when specifying a requirement? Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!