Dates
Posted by Hal Turchin on October 04, 2001 10:59 AM
I have a spreadsheet that's being used as a form letter.
In cell b4 I have a date 09/01/01.
In cell e10 I want a payment date....
If the date(day) is less than 10 I want the payment date to be the 10th of the month.If the date(day) is 10 or greater, I want the payment date to be the 25th of the month.
I need a formula to do this.
Thanks for your help.
Posted by IML on October 04, 2001 11:07 AM
You could use
=DATE(YEAR(B4),MONTH(B4),(DAY(B4)<10)*10+(DAY(B4)>=10)*25)
Posted by IML on October 04, 2001 11:10 AM
never mind
I assume you wouldn't a bill date of 9/25 for a 9/30 date.
back to the drawing board
Posted by Barrie Davidson on October 04, 2001 11:11 AM
That's a neat use of true/false, never would have thought of that on my own. My initial thought was to an IF statement (obviously not as slick as your solution).
Regards,
Barrie
Posted by Barrie Davidson on October 04, 2001 11:15 AM
Re: never mind
If Hal wants the 25th of the following month (which I assume is what he wants), you could tweak your formula to
=DATE(YEAR(B4),MONTH(B4)*(DAY(B4)<10)+(MONTH(B4)+1)*(DAY(B4)>=10),(DAY(B4)<10)*10+(DAY(B4)>=10)*25)
BarrieBarrie Davidson
Posted by Hal Turchin on October 04, 2001 11:18 AM
Tried the formula.......didn't work. I keep getting an error #NAME?
I don't need the following month, must be same month.
Hal
Posted by IML on October 04, 2001 11:20 AM
Re: never mind
I like your format better, but I assumed bills after the 25th would be due the tenth, and came up with
=DATE(YEAR(B4),MONTH(B4)+(DAY(B4)>=25),IF(OR(DAY(B4)>24,DAY(B4)<10),10,25))
I like what the date function does to the year when you plug in, say 12/28/01.
Posted by Barrie Davidson on October 04, 2001 11:23 AM
Re: never mind
Yeah, it's nice when the computer "knows" what to do without being told to the nth degree of detail.
Barrie
Posted by Barrie Davidson on October 04, 2001 11:26 AM
Hal, this works okay for me. Double check that you have the exact syntax as IML has posted (easiest way to do that is to copy from the posting).
Regards,
BarrieBarrie Davidson
Posted by IML on October 04, 2001 11:29 AM
None of these formulas are add- in formulas so I'm confused by this.
This is fine then for a formula standpoint. I'd worry about have a bill due on 9/25 for something I bought 9/30, though.
good luck
Posted by Hal Turchi on October 04, 2001 11:31 AM
Re: never mind
This last one worked fine.
thanks guys
Hal
