Modifying a complex date formual to count months

C

Colin Hayes

Hi all

I hope someone can help with modifying an existing formula.

In A21 I have this formula :

=IF(ISNUMBER(B21),IF(MOD(B21,12)=1,"Year "&0&0+INT(B21/12),""),"")

It is copied down to A140.


In B21 to B40 , I have the numbers 1 to 120.

The effect of the formula in column A to count the years from 0 to 10.

Where the cell in column A does not make a full year , it is left blank.
Like this

A B
Year 00 1
2
3
4
5
6
7
8
9
10
11
12
Year 01 13
14
15
16
17
18
19
20
21
22
23
24
Year 02 25
26


and so on.

I'd like to modify the code so that the 'blank cells in column A show
the month within the respective year. It would look like this :


A B

Year 00 1
02 2
03 3
04 4
05 5
06 6
07 7
08 8
09 9
10 10
11 11
12 12
Year 01 13
02 14
03 15
04 16
05 17
06 18
07 19
08 20
09 21
10 22
11 23
12 24
Year 02 25
02 26


Can someone advise?
 
C

Charabeuh

Colin Hayes a formulé ce samedi :
Hi all

I hope someone can help with modifying an existing formula.

In A21 I have this formula :

=IF(ISNUMBER(B21),IF(MOD(B21,12)=1,"Year "&0&0+INT(B21/12),""),"")

It is copied down to A140.


In B21 to B40 , I have the numbers 1 to 120.

The effect of the formula in column A to count the years from 0 to 10.

Where the cell in column A does not make a full year , it is left blank. Like
this

A B
Year 00 1
2
3
4
5
6
7
8
9
10
11
12
Year 01 13
14
15
16
17
18
19
20
21
22
23
24
Year 02 25
26


and so on.

I'd like to modify the code so that the 'blank cells in column A show the
month within the respective year. It would look like this :
Click to expand...
Hello,

You could try this formula:
=IF(ISNUMBER(B21),IF(MOD(B21,12)=1,"Year
"&0&0+INT(B21/12),REPT("0",2-LEN(2+MOD(B21-2,12))) &(
2+MOD(B21-2,12))),"")
 
C

Charabeuh

Hello,

You could try this formula:
=IF(ISNUMBER(B21),IF(MOD(B21,12)=1,"Year
"&0&0+INT(B21/12),REPT("0",2-LEN(2+MOD(B21-2,12))) &(
2+MOD(B21-2,12))),"")
 
C

Colin Hayes

=IF(ISNUMBER(B21),IF(MOD(B21,12)=1,"Year
"&0&0+INT(B21/12),REPT("0",2-LEN(2+MOD(B21-2,12))) &(
2+MOD(B21-2,12))),"")


HI

Yes - that's fantastic. It worked perfectly first time.

Thank you for your time and considerable expertise.



Best Wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Function 2
Copy value according to date 1
Count entries in the column translating to minutes. 5
Time query 9
Count months away betwwen a strat and end date by month 1
Peak call times/hours 2
Find then highlight in yellow 6
How to get largest date from a Vlookup 1

Top