Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Categorize certain times of day as other days
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate # of days from two different times | New Users to Excel | |||
Difference between dates/times in Days & Hours | Excel Discussion (Misc queries) | |||
Categorize Income and Expenses? | Excel Discussion (Misc queries) | |||
Difference in two times over days | Excel Worksheet Functions | |||
Need to calulate a sum for once a month X #days X #times used per. | Excel Worksheet Functions |