So I'm trying to write a macro to count a number of records built on a shift
and output the data in a form similar to the one below.
Date Count
6/20/08 5
6/21/08 6

The problem i'm having is that our 2nd shift runs from 6:00 pm - 4:00 am and
I would like all of the data from that shift to be counted on the shift start
date. Ie, if i have a record on 6/21/08 2:15 AM that should be counted as
6/20/08.

Assuming that your first shift runs from 8:00 Am to 6:00 PM, is is possible
to add six hours to the time to bring both shifts into the correct day?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Brad" wrote in message
...
So I'm trying to write a macro to count a number of records built on a
shift
and output the data in a form similar to the one below.
Date Count
6/20/08 5
6/21/08 6

The problem i'm having is that our 2nd shift runs from 6:00 pm - 4:00 am
and
I would like all of the data from that shift to be counted on the shift
start
date. Ie, if i have a record on 6/21/08 2:15 AM that should be counted as
6/20/08.

1st shift runs from 6a - 4p

"Sandy Mann" wrote:

Assuming that your first shift runs from 8:00 Am to 6:00 PM, is is possible
to add six hours to the time to bring both shifts into the correct day?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Brad" wrote in message
...
So I'm trying to write a macro to count a number of records built on a
shift
and output the data in a form similar to the one below.
Date Count
6/20/08 5
6/21/08 6

The problem i'm having is that our 2nd shift runs from 6:00 pm - 4:00 am
and
I would like all of the data from that shift to be counted on the shift
start
date. Ie, if i have a record on 6/21/08 2:15 AM that should be counted as
6/20/08.

Fine, adding six hours will still put both shifts in the day that you wan to
record the Records in.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Brad" wrote in message
...
1st shift runs from 6a - 4p

"Sandy Mann" wrote:

Assuming that your first shift runs from 8:00 Am to 6:00 PM, is is
possible
to add six hours to the time to bring both shifts into the correct day?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Brad" wrote in message
...
So I'm trying to write a macro to count a number of records built on a
shift
and output the data in a form similar to the one below.
Date Count
6/20/08 5
6/21/08 6

The problem i'm having is that our 2nd shift runs from 6:00 pm - 4:00
am
and
I would like all of the data from that shift to be counted on the shift
start
date. Ie, if i have a record on 6/21/08 2:15 AM that should be counted
as
6/20/08.

Yup, that works. I actually set up an if then that would subtract 12 hrs if
the user selected 2nd shift. so the code looks like.

If enteredShift = "2" Then
rawDataDate = Cells(x, 12).Value - TimeSerial(12, 0, 0)
Else
rawDataDate = Cells(x, 12).Value
End If

"Sandy Mann" wrote:

Fine, adding six hours will still put both shifts in the day that you wan to
record the Records in.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Brad" wrote in message
...
1st shift runs from 6a - 4p

"Sandy Mann" wrote:

Assuming that your first shift runs from 8:00 Am to 6:00 PM, is is
possible
to add six hours to the time to bring both shifts into the correct day?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Brad" wrote in message
...
So I'm trying to write a macro to count a number of records built on a
shift
and output the data in a form similar to the one below.
Date Count
6/20/08 5
6/21/08 6

The problem i'm having is that our 2nd shift runs from 6:00 pm - 4:00
am
and
I would like all of the data from that shift to be counted on the shift
start
date. Ie, if i have a record on 6/21/08 2:15 AM that should be counted
as
6/20/08.

Mmmm.... I see that these *backward* American style dates are confusing me,
Bob is doing it the right way round.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Fine, adding six hours will still put both shifts in the day that you wan
to record the Records in.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Brad" wrote in message
...
1st shift runs from 6a - 4p

"Sandy Mann" wrote:

Assuming that your first shift runs from 8:00 Am to 6:00 PM, is is
possible
to add six hours to the time to bring both shifts into the correct day?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Brad" wrote in message
...
So I'm trying to write a macro to count a number of records built on a
shift
and output the data in a form similar to the one below.
Date Count
6/20/08 5
6/21/08 6

The problem i'm having is that our 2nd shift runs from 6:00 pm - 4:00
am
and
I would like all of the data from that shift to be counted on the
shift
start
date. Ie, if i have a record on 6/21/08 2:15 AM that should be counted
as
6/20/08.

If the times are only between 6:00PM and 4:00AM, you could use this formula

=SUMPRODUCT(--(INT(Sheet2!$A$2:$A$200-"4:00:01")=Sheet3!A2))

--
__________________________________
HTH

Bob

"Brad" wrote in message
...
So I'm trying to write a macro to count a number of records built on a
shift
and output the data in a form similar to the one below.
Date Count
6/20/08 5
6/21/08 6

The problem i'm having is that our 2nd shift runs from 6:00 pm - 4:00 am
and
I would like all of the data from that shift to be counted on the shift
start
date. Ie, if i have a record on 6/21/08 2:15 AM that should be counted as
6/20/08.