View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
Duke Carey Duke Carey is offline
external usenet poster
 
Posts: 1,081
Default Categorize certain times of day as other days

Excel stores date/time data as a number. Numbers to the left of the decimal
are the date portion; numbers to the right are the day portion.

6:30AM translates into .2708333, or just over one-quarter of a day.

Subtract that amount from each of your date/time values & format the result
as a short date. That'll convert values that fall at or before 6:30 to the
previous day, and leave the others as of the actual date


"ecd211" wrote:

I am trying to organize data by business day but all I have right now is a
column with date and time data. I would like everything from before a
certain time on a particular day to be categorized as the previous day.
Basically I want my day to reset at 6:30 AM, so that everything before then
appears as the day before. Would like to be able to display the business day
in a separate column.

I have too many dates to use nested IF statements, but I'm hoping there is
an easier way to do this anyway. Any suggestions are very welcome. Thanks!