Modifying a complex date formual to count months
 

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?

Modifying a complex date formual to count months
 

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 :

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))),"")




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?

Modifying a complex date formual to count months
 

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))),"")

Modifying a complex date formual to count months
 

In article , Charabeuh
writes


=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

Modifying a complex date formual to count months
 

Thank you for your feedback


In article , Charabeuh
writes


=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