Date & Time Woes.

Leeinsa

Board Regular
Joined
Jul 24, 2009
Messages
96
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,880
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

Board Regular
Joined
Jul 24, 2009
Messages
96
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

Legend
Joined
Jun 19, 2002
Messages
63,651
To return the time as a number and not text this would be better

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

Forum statistics

Threads
1,085,294
Messages
5,382,769
Members
401,804
Latest member
RB85

Some videos you may like

This Week's Hot Topics

Top