Need an Excel expert

[naiku]

I need some help with Excel. I have 3 columns of data:

1 - Job
2 - Machine
3 - Date

The data basically shows a job, running on a machine, at a particular date. A job runs on several machines over various dates. What I want to achieve, is to have a single line per job, have the machine numbers running across the top of the sheet (column 2 onwards) and populate the date under the machine for the job.

Kind of like setting up a pivot table, but instead of counting the dates, I simply want it to show the date.

Any way to do this? I want it to be repeatable on a weekly basis, so the less manual steps the better.

Thanks.

[Godsspeed]

give me a second......

you would like the following:

| Machine 1 | Machine 2 | Machine 3 | Machine 4 | etc..
Job 1 | 4/18/12 | 4/18/12 | 4/19/12 |
Job 3:| 4/18/12 | 4/19/12 |
(my formatting didnt stay.. the vertical lines rep a column.....

correct?


Im REALLY close, but have an issue if a job is on the same machine for 2+days......hmmm

[naiku]

give me a second......

you would like the following:

| Machine 1 | Machine 2 | Machine 3 | Machine 4 | etc..
Job 1 | 4/18/12 | 4/18/12 | 4/19/12 |
Job 3:| 4/18/12 | 4/19/12 |
(my formatting didnt stay.. the vertical lines rep a column.....

correct?


Im REALLY close, but have an issue if a job is on the same machine for 2+days......hmmm

Yep, that is correct. And a job can be on a machine for more than 1 day (will have to double check on that though).

[naiku]

OK, so I managed to figure the first part out. But now am stuck with a different problem. I am trying to calculate start/end times for jobs running on a machine based on a 20 hour day, 5 day work week. Shift starts at 6am, and ends at 2am (the following day).

I am using the following formula in cell C1:

=WORKDAY(B1,CEILING((B2+MOD(B1,1)-B$3)/(B$4-B$3),1)-1)+MOD(B1,1)+B2-CEILING(MOD(B1,1)+B2-B$3,B$4-B$3)+B$4-B$3

B1 - Start Time (5/4/12 11:00am)
B2 - Run Hours (4 hours)
B3 - Shift Start (6:00am)
B4 - Shift End (2:00am)

C1 would then show End Time as 5/4/12 3:00pm.

If my Shift End time is before midnight, then it works no problem. As soon as I change that time to 2am, I get a #NUM! error.

Any ideas? It's got me stumped.

Thanks.

[GreenandChrome]

OK, so I managed to figure the first part out. But now am stuck with a different problem. I am trying to calculate start/end times for jobs running on a machine based on a 20 hour day, 5 day work week. Shift starts at 6am, and ends at 2am (the following day).

I am using the following formula in cell C1:

=WORKDAY(B1,CEILING((B2+MOD(B1,1)-B$3)/(B$4-B$3),1)-1)+MOD(B1,1)+B2-CEILING(MOD(B1,1)+B2-B$3,B$4-B$3)+B$4-B$3

B1 - Start Time (5/4/12 11:00am)
B2 - Run Hours (4 hours)
B3 - Shift Start (6:00am)
B4 - Shift End (2:00am)

C1 would then show End Time as 5/4/12 3:00pm.

If my Shift End time is before midnight, then it works no problem. As soon as I change that time to 2am, I get a #NUM! error.

Any ideas? It's got me stumped.

Thanks.

Format cell to recognize/transpose to 24-hr time?

[funny123]

Hello,
Salutation to this one of the forum site.
Myself Jyaky Petter from the nation of India
at this point we are going away to thrash out in the automobile discussion
We can argue many kind of debate from the forum
We not only discuss but also dig up a lot more knowledge from
Various Google experts.
Thank you………………………………………………………..
…………………………………………………
…………………………………..

2013 MERCEDES BENZ SL-CLASS