You could make use of the WEEKDAY function in another column - it will return
numbers 1 to 7 dependent on the day of the week.

Hope this helps.

Pete

"MJW" wrote:

Thanks Pete, it worked great! While I wasn't concerned with the date part, I
am faced with now extracting the "day" frequencies (the purpose behind this
particular effort is to determine the historical work volume [where a row
represents an effort, task or case] for weekend coverage between the three
shifts), so it's always good to learn as much info as I can, so I appreciate
the background as well.

Thanks

Jamie

"Pete_UK" wrote:

Sorry, I missed a bracket off the end - it should be:

=IF(MOD(A1,1)<=1/3,"A",IF(MOD(A1,1)<=2/3,"B","C"))

Also, I see you had lots of replies to your other thread after I went
to watch the football, but using SUBSTITUTE is an easy way of getting
rid of the spaces and thus render the data into a more standardised
format.

Hope this helps.

Pete

On Sep 20, 11:09 pm, Pete_UK wrote:
A bit of background first - Excel stores dates as the number of days
that have elapsed since some reference date (1st Jan 1900), and time
is stored as a fraction of a 24-hour day. So, the date part of a date-
time field will always be integer, and the time part will always be
fractional, so you can use INT and MOD to get at the different parts.
In your case you don't seem to be interested in the date part.

If you want your three time periods to be referred to as A, B and C,
you could use this formula, assuming that the date-time data is in A1:

=IF(MOD(A1,1)<=1/3,"A",IF(MOD(A1,1)<=2/3,"B","C")

MOD(A1,1) will give the time part and if it is less than or equal to
1/3 (i.e. the first third of a day, or before 8:00am) set the period
to "A" ... and I think you can work out the rest.

Substitute other identifiers for A, B and C, as required, then copy
the formula down.

Hope this helps.

Pete

On Sep 20, 10:52 pm, MJW wrote:



Hi Again All,

I have a slew of dates and times (measured out as far as seconds) in a
12-month range, and they need to be grouped as "Rows where mm/dd/yy tt:tt:tt"
is from 0:00 (midnight)-8:00am, 8:01am-4:00pm, and 4:01pm-11:59pm. I have an
auxiliary column I've placed adjecent to this column, but I can't seem to get
it done. Pivot Tables and grouping won't do it, because there are so many
unique values (6000) that the pivot table simply won't generate. (I've
attempted creating it 15 times using the Month column for rows and the d/t
column for columns, and it goes through the pivot creation without issue, but
when you click "Finish", nothing happens.) Any ideas?

Thanks,

Jamie

(Guess it's time for me to start looking at the questions to see what I can
help with, if only to offset my increasing number of requests. :) )- Hide quoted text -

- Show quoted text -