right function on a mm/dd/yy tt:tt:tt field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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. :) )
 
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
 
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
 
You are allowed up to 8,000 unique values before pivot tables die an untimely
death. that being said if your data is down to the second then youwill hit
that limit if you have much more than 8,000 source rows. Perhaps you could
split the date and time into 2 seperate fields??? You will have less tahtn
8000 date valeus and less than 8000 time values. That might get you around
the 8,000 limit...

Assuming your date&time is in A1 put this formula in B1
=ROUND(A1, 0) To get your date. Time is stored as the decimal.
and this formula in C1
=A1-B1 To Get the time

Don't include the DateTime field in the source data of your pivot table.
Let me know if that works...
 
Jim,

And so you were right--it's a report I generate monthly, and my recollection
of the row volume was related to the same report I'd done a little more than
a month ago--ahh, what a difference a month makes--6500 rows to just over
8800. Fare thee well, Pivot Table. :) I liked your solution, but in case
you happen to need something similar in the future, take a look at Pete's--it
required a little more depth in the formula but less auxiliary columns and
easy identifiers. Thanks for the help, much appreciated!

Sincerely,

Jamie
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top