Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Juco
 
Posts: n/a
Default 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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
Juco
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
Juco
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Formula Robyn Bellanger Excel Discussion (Misc queries) 2 December 17th 04 08:25 PM
Date formula Robyn Bellanger Excel Discussion (Misc queries) 2 December 16th 04 01:41 AM
Need help troubleshooting an array formula XLXP on Win2K KR Excel Worksheet Functions 1 December 13th 04 08:41 PM
How do I add a date formula to a cell but hide the contents with . Emzy Wemzy Excel Discussion (Misc queries) 2 December 12th 04 02:48 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM


All times are GMT +1. The time now is 08:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"