Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing times into shifts
Hi there!
What i have: I have a telephone bill in an excel spreadsheet and im trying to perform a cost analysis on my company's calls during certain shifts. the phone bill comes with a column that breaks down the time of the call in this format HH:MM and then AM or PM. What i need: In this cost analysis i dont need specific times what i need is shifts. I need to set shifts from 8:00 AM - 4:30 PM to be shift 1 from 4:31 PM - 7:59 AM to be shift 2. How do i go about automatically taking the information in the time column and have it run through those conditions and populate in another column as a shift and not a specific time. Please help, i would much appreciate it -- thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing times into shifts
Try this formula -
=IF(AND((VALUE(CONCATENATE("",A6,"")))=TIMEVALUE( "8:00 AM"),VALUE((CONCATENATE("",A6,"")))<TIMEVALUE("4:3 1 PM")),"Shift1","Shift 2") Test Data that I placed in A6 - A9 and formula in B6 - B9 with A6 - A9 formmatted as "h:mm AM/PM" 10:11 AM Shift1 2:33 PM Shift1 1:59 PM Shift1 8:28 PM Shift 2 -- Joe Mac "jcontrer" wrote: Hi there! What i have: I have a telephone bill in an excel spreadsheet and im trying to perform a cost analysis on my company's calls during certain shifts. the phone bill comes with a column that breaks down the time of the call in this format HH:MM and then AM or PM. What i need: In this cost analysis i dont need specific times what i need is shifts. I need to set shifts from 8:00 AM - 4:30 PM to be shift 1 from 4:31 PM - 7:59 AM to be shift 2. How do i go about automatically taking the information in the time column and have it run through those conditions and populate in another column as a shift and not a specific time. Please help, i would much appreciate it -- thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
End of month help with shifts | Excel Worksheet Functions | |||
Print same Excel sheet several times but while changing footer? | Excel Discussion (Misc queries) | |||
More column shifts ... | Excel Discussion (Misc queries) | |||
Set up schedule for 3 different shifts | Excel Discussion (Misc queries) | |||
How do I keep excel from changing minute and second times into AM. | Excel Discussion (Misc queries) |