Opening Balance Date

[Manesh]

Dear Chandoo

I am trying to calculate the date for the Opening Balance in Column C. NB: the dates may change in the future and the cells locations of the dates may also change as new data becomes available.

The value for the opening balance date must be placed next to the value of the closing balance date. In the first year (1982), the value will be equal to the first date in Column A. Hence C17 should be equal to A2.

However, in subsequent years, the opening balance date must be equal to the closing balance date of the previous year. Hence in 1983, the opening balance date (C35) must be equal to (A17) which is the last date of 1982.

I have attached some sample data herewith.

Thank you.

Regards,
Manesh

 

[Mike H..]

Dear Chandoo

I am trying to calculate the date for the Opening Balance in Column C. NB: the dates may change in the future and the cells locations of the dates may also change as new data becomes available.

The value for the opening balance date must be placed next to the value of the closing balance date. In the first year (1982), the value will be equal to the first date in Column A. Hence C17 should be equal to A2.

However, in subsequent years, the opening balance date must be equal to the closing balance date of the previous year. Hence in 1983, the opening balance date (C35) must be equal to (A17) which is the last date of 1982.

I have attached some sample data herewith.

Thank you.

Regards,
Manesh

Hi,

Check out your workbook.

 

[Hui]

C17: =IF(B17<>"",MIN(IF(YEAR(B17)=YEAR($A$2:$A$643),$A$2:$A$643,FALSE)),"") Ctrl+Shift+Enter
Then copy down

 

[Manesh]

Hi Hui
The formula you have suggested does not work. It brings in the correct date for C17 but fails thereafter. For example, the date in C35 should be the last date for the previous year. Instead it has brought in the first date for the next year.

Please refer to the file uploaded yesterday. In Column D i show the values that should appear in the solution.

Thank you for your continued assistance.

Best regards,

Manesh

 

[Hui]

Manesh

If I open your file above
Copy/Paste the formula =IF(B17<>"",MIN(IF(YEAR(B17)=YEAR($A$2:$A$643),$A$2:$A$643,FALSE)),"")
into C17
Edit (F2) C17 then press Ctrl+Shift+Enter
Now select C17, Copy
Move to C18 Hold Shift and press and hold Arrow Down until you get to C643
Then press Enter
C35 for me shows 4 Jan 83 as it should

See attached file:

 

[Manesh]

Hello Hui,

Thank you for your reply.

If you take a look at my data file, you will notice that after 1982, we want the opening balance date to be the last date of the previous year.

I have reattached a file that shows in Column D, the values that should appear in Column C. This may assist you with ease of reference.

The method that you have provided gives us the first date for the calender year. This will only apply in the first instance.

Please also bear in mind that the dates may change as new data becomes available.

Thank you for your continued support.

Best regards,

Manesh

 

[Hui]

C18: =IF(B18<>"",MAX(B$2:B17),"")
Copy down

 

[Manesh]

Thank you Hui,

This works perfectly together with the first formular.

Best regards,

Manesh