sc0013z

i have a column that list dates, and i want to change it to the ff:

1/1/** - 1/15/** should return a value of 1/15/**
1/16/** - 1/31or1/30/** should return a value of end of the month

jvick125

iTrader: (2)

what are you trying to do?

SubyN00by

iTrader: (15)

he's trying to conquer the world!!!

I don't know how to do it without converting the date field to a special, and entering your dates in as MMDDYYYY and letting the logic take care of the rest.

Is the info already in a spreadsheet, or is it something that someone will be entering?

sc0013z

the info is already there

2 pay cycles 15th and 30th or last day of the month

i need to have those dates from 1st to the 15th show pay period of 15th and those from 16th to last day of the month to show pay period of last day of the month.

I am manually changing it right now, i have been playing with some formulas but i cant get it right

SubyN00by

iTrader: (15)

so first off all, make two key cells, one put in 1/1/2007, the other put in 1/15/2007, say you put them in K1 and L1, and say your date cell is C1

=IF(C1>=K1,IF(C1>=L1,"1/31/2007","1/15/2007"),"undefined")

so what you have is IF Date >= 1/1/2007 & Date >= 1/15/07, it should be 1/31/2007, otherwise it should be 1/15/2007.

Also, for error checking, if Date is less than 1/1/2007 return error

sc0013z

what if there are different years involved, 2005, 2006, 2007? that was also my formula in mind, but then my dates have different years, i was trying to put "*" on the years but wont take it.

SubyN00by

iTrader: (15)

that ones a tuffy...I can do it C or PHP, but excel...I dunno about that one...

sc0013z

thanks, all i can think of right now is sorting by year then go from there. much better than manually doing it.

SubyN00by

iTrader: (15)

sort by date.

then make the first one like 1/1/2007 and drag it down until 1/15/07, then drag that down till 2/1/07, then drag...etc.