Home |
Search |
Today's Posts |
#1
|
|||
|
|||
addition to my date formula...required
Ok I hope last piece of the jigsaw..I have started a new post as everything
up to this point is working ok. The formula below works fine but need to add to it, if at all possible, I could do this manually if its too complicated. =CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"") This formula in cell M7 and works great. This is a booking form and depending on the months depends on the price ie 5 6 7 8 are months and 150 & 200 are £s If someone books last week of month 6 and first week of month 7 then I would need to add a further £50 as this is across the 2 cost bands The trigger for that cell would be H7 as this would show it was clicking into the next month. start date which is cell D7 the person puts 5,6,7 or 8 (mm) end date which is cell H7 the person puts 5,6,7 or8 (mm) If D7 has 6 (mm) and H7 has 7 (mm) then I need to add a further £50 to the total cost (I think would be ok to add £25 to each week) as they have now moved from the £150 to the £200 price band. thanks " if cell D7 = 5 or 6 then cell M7 to show 150 if cell D7 = 7 or 8 then cell M7 to show 200 The 5 6 7 8 refer to months ie May/June/July/August The 150/200 are £s" |
#2
|
|||
|
|||
Try in M7:
=IF(H7=D7+1,CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,20 0,"")+50,CHOOSE(MATCH(D7,{ 0;5;7;9},1),"",150,200,"")) Correction to formula suggested, sorry Try instead in M7: =IF(AND(D7=6,H7=7),CHOOSE(MATCH(D7,{0;5;7;9},1),"" ,150,200,"")+50,CHOOSE(MAT CH(D7,{0;5;7;9},1),"",150,200,"")) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#3
|
|||
|
|||
Max when I put the corrected formula in it comes up #NAME? unless I use
months 6 snd 7 in which case it gives the correct answer . If both dates are in month 6 so 6 6 or 7 and 8 I get #NAME? "Max" wrote in message ... Try in M7: =IF(H7=D7+1,CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,20 0,"")+50,CHOOSE(MATCH(D7,{ 0;5;7;9},1),"",150,200,"")) Correction to formula suggested, sorry Try instead in M7: =IF(AND(D7=6,H7=7),CHOOSE(MATCH(D7,{0;5;7;9},1),"" ,150,200,"")+50,CHOOSE(MAT CH(D7,{0;5;7;9},1),"",150,200,"")) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
Think you probably got hit by several inevitable line breaks/wraps when you
copied and pasted the formula from the post. Try pasting *direct* into the fornula bar for M7, then correct the obvious line breaks via using backspace/delete key to restore where the formula gets "chopped". I've just retested it here and it works ok. If you still have difficulty, send me a copy of your book, and I'll set it up for you. Either: demechanik <atyahoo<dotcom or xdemechanik <atyahoo<dotcom (both valid) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Juco wrote in message ... Max when I put the corrected formula in it comes up #NAME? unless I use months 6 snd 7 in which case it gives the correct answer . If both dates are in month 6 so 6 6 or 7 and 8 I get #NAME? |
#5
|
|||
|
|||
Max,
You are correct I didn`r realise it was broken It works perfectly now. Thanks very much for your help. Juco "Max" wrote in message ... Think you probably got hit by several inevitable line breaks/wraps when you copied and pasted the formula from the post. Try pasting *direct* into the fornula bar for M7, then correct the obvious line breaks via using backspace/delete key to restore where the formula gets "chopped". I've just retested it here and it works ok. If you still have difficulty, send me a copy of your book, and I'll set it up for you. Either: demechanik <atyahoo<dotcom or xdemechanik <atyahoo<dotcom (both valid) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Juco wrote in message ... Max when I put the corrected formula in it comes up #NAME? unless I use months 6 snd 7 in which case it gives the correct answer . If both dates are in month 6 so 6 6 or 7 and 8 I get #NAME? |
#6
|
|||
|
|||
Great to hear that <g !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Juco wrote in message k... Max, You are correct I didn`r realise it was broken It works perfectly now. Thanks very much for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formula | Excel Discussion (Misc queries) | |||
Date formula | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
How do I add a date formula to a cell but hide the contents with . | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |