Date Issue

[Brad]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bob Phillips]

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))

 

[Brad]

1st shift runs from 6a - 4p

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Brad]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk