Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Due date changes when new value is inputted in a different column
I have this if statement
=IF(I4:I150<=10101,"",DATE(YEAR(I4:I150),MONTH(I4: I150)+G4:G150,DAY(I4:I150))) and it works great when it looks at this one column. It returns the answer I want. However, I need it to look at additional columns that will superceed the previous one without erasing their date and still show the result in the original column. Column "F" shows the result. Columns "I-Z" give the date superceeding each occurrance. i.e. "F" returns a date based on column "G" and "I". When a new date is put in "J" I want column "F" to show the update. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Due date changes when new value is inputted in a different column
It's long but it seems to work:
=MID(CONCATENATE(G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2, R2,S2,T2,U2,V2,W2,X2,Y2,Z2),1,5)*1 WHy it works: When you concatenate cells, excel treats them as text. This means each date will actually be read as the date serial in this concatenation. Since date serials are always 5 digits you pull the left most five and that means that as the users fill in dates, the first cell refrence to have a value will get precedence. If you wanted to give precedence to items more to the right (Z trumps y, then just reverse the list.) Multiplying it by one turns the date serial from text to numeric, so when you format as date it will show a classic date instead of a serial. *NOTE* This breaks down if a user enters a value other then a date in a cell. "John" wrote in message ... I have this if statement =IF(I4:I150<=10101,"",DATE(YEAR(I4:I150),MONTH(I4: I150)+G4:G150,DAY(I4:I150))) and it works great when it looks at this one column. It returns the answer I want. However, I need it to look at additional columns that will superceed the previous one without erasing their date and still show the result in the original column. Column "F" shows the result. Columns "I-Z" give the date superceeding each occurrance. i.e. "F" returns a date based on column "G" and "I". When a new date is put in "J" I want column "F" to show the update. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Due date changes when new value is inputted in a different col
Many Thanks!!!! You have no idea how helpful this has been. Thank you.
"Oorang" wrote: It's long but it seems to work: =MID(CONCATENATE(G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2, R2,S2,T2,U2,V2,W2,X2,Y2,Z2),1,5)*1 WHy it works: When you concatenate cells, excel treats them as text. This means each date will actually be read as the date serial in this concatenation. Since date serials are always 5 digits you pull the left most five and that means that as the users fill in dates, the first cell refrence to have a value will get precedence. If you wanted to give precedence to items more to the right (Z trumps y, then just reverse the list.) Multiplying it by one turns the date serial from text to numeric, so when you format as date it will show a classic date instead of a serial. *NOTE* This breaks down if a user enters a value other then a date in a cell. "John" wrote in message ... I have this if statement =IF(I4:I150<=10101,"",DATE(YEAR(I4:I150),MONTH(I4: I150)+G4:G150,DAY(I4:I150))) and it works great when it looks at this one column. It returns the answer I want. However, I need it to look at additional columns that will superceed the previous one without erasing their date and still show the result in the original column. Column "F" shows the result. Columns "I-Z" give the date superceeding each occurrance. i.e. "F" returns a date based on column "G" and "I". When a new date is put in "J" I want column "F" to show the update. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Due date changes when new value is inputted in a different col
Oorang,
I have one additional request. I want the cell to be empty until I use the columns. How do I keep the error (#VALUE!) from showing up? "John" wrote: Many Thanks!!!! You have no idea how helpful this has been. Thank you. "Oorang" wrote: It's long but it seems to work: =MID(CONCATENATE(G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2, R2,S2,T2,U2,V2,W2,X2,Y2,Z2),1,5)*1 WHy it works: When you concatenate cells, excel treats them as text. This means each date will actually be read as the date serial in this concatenation. Since date serials are always 5 digits you pull the left most five and that means that as the users fill in dates, the first cell refrence to have a value will get precedence. If you wanted to give precedence to items more to the right (Z trumps y, then just reverse the list.) Multiplying it by one turns the date serial from text to numeric, so when you format as date it will show a classic date instead of a serial. *NOTE* This breaks down if a user enters a value other then a date in a cell. "John" wrote in message ... I have this if statement =IF(I4:I150<=10101,"",DATE(YEAR(I4:I150),MONTH(I4: I150)+G4:G150,DAY(I4:I150))) and it works great when it looks at this one column. It returns the answer I want. However, I need it to look at additional columns that will superceed the previous one without erasing their date and still show the result in the original column. Column "F" shows the result. Columns "I-Z" give the date superceeding each occurrance. i.e. "F" returns a date based on column "G" and "I". When a new date is put in "J" I want column "F" to show the update. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capturing The Date Of When Data Is Inputted | Excel Discussion (Misc queries) | |||
Data Changes itself after I have inputted. | Excel Discussion (Misc queries) | |||
Getting Last Value that inputted | Excel Discussion (Misc queries) | |||
Record date cell is inputted | Excel Discussion (Misc queries) | |||
adding months to an inputted date | Excel Discussion (Misc queries) |