| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End of month help with shifts
Wonder if anyone can help he
From a raw report I have a date and time in column A (As mm/dd/yyyy hh:mm:ss) and a shift in column D (ie a,b,c,d). In column B, I use the following equation: =CONCATENATE(MONTH(A2),"/",IF(HOUR(A2)=7,DAY(A2),DAY(A2)-1),"/",D2,IF(OR(HOUR(A2)<7,HOUR(A2)=19),"N","D")) We run shift from 7 to 7 on a 24 hr bases and the above equation works ok apart from the start of the nex month. errors like below come up 1/31/AN 2/0/AN (error) 2/1/BD I would like the error to read 1/31/AN until 7:00 Am on the 2/1/BD. Any suggest welcome? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
End of month help with shifts
Try:
=CONCATENATE(IF(HOUR(A2)=7,MONTH(A2) &"/" & DAY(A2),MONTH(A2-1) & "/" &DAY(A2-1)),"/",D2,IF(OR(HOUR(A2)<7,HOUR(A2)=19),"N","D")) You need to subract 1 from the Date BEFORE assigning month and day. HTH "MijC" wrote: Wonder if anyone can help he From a raw report I have a date and time in column A (As mm/dd/yyyy hh:mm:ss) and a shift in column D (ie a,b,c,d). In column B, I use the following equation: =CONCATENATE(MONTH(A2),"/",IF(HOUR(A2)=7,DAY(A2),DAY(A2)-1),"/",D2,IF(OR(HOUR(A2)<7,HOUR(A2)=19),"N","D")) We run shift from 7 to 7 on a 24 hr bases and the above equation works ok apart from the start of the nex month. errors like below come up 1/31/AN 2/0/AN (error) 2/1/BD I would like the error to read 1/31/AN until 7:00 Am on the 2/1/BD. Any suggest welcome? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
End of month help with shifts
TA VERY MUCH WORKS PERFECT.
"Toppers" wrote: Try: =CONCATENATE(IF(HOUR(A2)=7,MONTH(A2) &"/" & DAY(A2),MONTH(A2-1) & "/" &DAY(A2-1)),"/",D2,IF(OR(HOUR(A2)<7,HOUR(A2)=19),"N","D")) You need to subract 1 from the Date BEFORE assigning month and day. HTH "MijC" wrote: Wonder if anyone can help he From a raw report I have a date and time in column A (As mm/dd/yyyy hh:mm:ss) and a shift in column D (ie a,b,c,d). In column B, I use the following equation: =CONCATENATE(MONTH(A2),"/",IF(HOUR(A2)=7,DAY(A2),DAY(A2)-1),"/",D2,IF(OR(HOUR(A2)<7,HOUR(A2)=19),"N","D")) We run shift from 7 to 7 on a 24 hr bases and the above equation works ok apart from the start of the nex month. errors like below come up 1/31/AN 2/0/AN (error) 2/1/BD I would like the error to read 1/31/AN until 7:00 Am on the 2/1/BD. Any suggest welcome? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Print Area Shifts | Excel Discussion (Misc queries) | |||
| More column shifts ... | Excel Discussion (Misc queries) | |||
| Set up schedule for 3 different shifts | Excel Discussion (Misc queries) | |||
| what command shifts all cells in a column up or down? | New Users to Excel | |||
| File Menu Shifts to Right | Excel Discussion (Misc queries) | |||
Hybrid Mode
