Date & Time Woes.

[Leeinsa]

Gents,
Hopefully someone can give me some guidance on the best way to implement the following issue. I have a spreadsheet detailing phone calls being made from our offices. The spreadsheet has a column detailing the date and time the call was placed. The date and time is formatted as follows:-

2011/01/14 09:09:00 AM
2011/01/13 07:43:00 PM

I need to use a VBA macro to identify if the call was placed during peak hours. My peak hours are 07:00 am to 19:00 pm Mon to Fri.

Can anyone help or guide me in the right direction?
Many thanks in advance,
Lee.

 

[VoG]

A formula can do this

Excel Workbook
	A	B
1	14/01/2011 09:09:00 AM	Peak
2	13/01/2011 07:43:00 PM	Offpeak
Sheet6

 

[mikerickson]

You don't need a macro, assuming that your date/time is in column A, this formula should do it.
=IF(AND(7 < HOUR(A1), HOUR(A1) < 19), "peak time", "off peak")

 

[Leeinsa]

Hey VoG and Mike,

Many thanks for the quick reply. I have just discovered a way of doing it in vba by setting variables as shown below and then comparing them. But maybe one of your method would be neater. I'll give them a whirl.

dayofcall = Format(Worksheets("DataInput2").Range("H" & a), "dddd")
timeofcall = Format(Worksheets("DataInput2").Range("H" & a), "hh:mm:ss")

Thanks again,

Lee.

 

[VoG]

To return the time as a number and not text this would be better

timeofcall = Worksheets("DataInput2").Range("H" & a) Mod 1

 

[VoG]

Sorry http://www.mrexcel.com/forum/showpost.php?p=2760350&postcount=5 wont work (thanks shg!).

Try

timeofcall = TimeValue(Format(Worksheets("DataInput2").Range("H" & a), "hh:mm:ss"))