Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 465
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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))),"")


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 465
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Count Formual with checked boxes Annie Excel Worksheet Functions 1 November 17th 09 06:41 AM
formual to determine if date falls on weekend, adjust date to Mond Bradley Excel Discussion (Misc queries) 4 November 21st 08 07:19 PM
need help modifying a complex column of formulas [email protected] Excel Discussion (Misc queries) 12 October 27th 07 10:21 PM
Count months away betwwen a strat and end date by month JY Excel Worksheet Functions 1 October 24th 06 02:51 PM
count number of months year to date coal_miner Excel Worksheet Functions 1 May 4th 05 02:41 PM


All times are GMT +1. The time now is 09:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"