Modifying a complex date formual to count months

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
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?
 
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))),"")
 
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))),"")
 
=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
 
Back
Top