Dropdown behaviour shows different data depending on selection

[TheJay]

FIrst of all, apologies for the title, I don't know how to change it and realised I wanted to use a tick box instead of a drop down as I was writing.

I have a spreadsheet which calculates the charge for a date range.

Now I want to introduce a tick box in C6, which changes whether the inclusive or exclusive end is shown in C7 and relevant data in C8. As well as the data changing, when the tick box is clicked, I'd like the text in C6 to change from 'Exclusive End' to 'Inclusive End'.

The idea is that I don't want anything in row D because I need to incorporate other data eventually in that row. Not sure how this is achieved, can it be done without hidden cells?

The forum wont let me attach the spreadsheet

 

[TheJay]

That's brilliant, thanks very much! I even managed to set up my Reset macro to add the word 'Reset' and delete it from the cell that is being validated to figure out whether the checkbox should appear or not.

 

[TheJay]

Could you please help me figure out how I can get this to work on leap year? The calculation is clearly out by a day.

I want this to be flexible enough to work whether the dates are in a leap year or not, but it should always be looking at whether within the full financial year (up to and including 31/03) there is an additional day.

 

[TheJay]

This is the formula calculating the number of days:

=IF(ISBLANK(C6),"",(DATEDIF(C5,C6-(B7),"d")))

 

[jasonb75]

Try it without datedif, for simple date calculations like his, the function is no needed.

=IF(ISBLANK(C6),"",C6-C5-B7)

 

[TheJay]

Thanks for your reply, the output is the same when I try replacing the formula with your suggestion. What else can I try? Is there a way to make Excel look at the period 01/04/YEAR to 31/03/FOLLOWING YEAR to check for a leap year day and then start the calculations after that?

 

[TheJay]

It would need to look at the dates entered and identify whether the end date input in C6 comes before 01/04 the following year and whether before 01/04 the following year, February contains an extra day.

 

[jasonb75]

Sorry, I wasn't following what you were asking before. I see it now.

I have done it before but can't remember how. I'll have a look through some of my old files and replies later to see if I can find it.

 

[TheJay]

Great, thank you.

 

[jasonb75]

I couldn't find the old thread / file. Before I look at ways to do what you need, thinking out of the box -

Start date 25/03/2020
End date 05/04/2020

Assuming end date inclusive, should the above dates be calculated as (£789.03/366*7)+(£789.03/365*5)

 

[TheJay]

Thanks for your reply. Yes, that looks right time.