Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
an
 
Posts: n/a
Default SUM Days (to 30), Months (to 12) and Years

Hello!

I have:

YY MM DD
2 9 20
4 3 15

I would like your help for the next result after sum periods:

YY MM DD
7 1 5

Thanks in advance.
an


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default SUM Days (to 30), Months (to 12) and Years

Hi Just Extend the logic posted to your previous question on years and months.

=SUM(A2:A3)+INT(SUM(B2:B3)/12)
in Cell B4
=MOD(SUM(B2:B3),12)+INT(SUM(C2:C3)/30)
in cell C4
=MOD(SUM(C2:C3),30)

Note
This will not be accurate due to variation in month lengths.

Regards

Roger Govier


an wrote:
Hello!

I have:

YY MM DD
2 9 20
4 3 15

I would like your help for the next result after sum periods:

YY MM DD
7 1 5

Thanks in advance.
an


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default SUM Days (to 30), Months (to 12) and Years

=YEAR(DATE(A1+A2,B1+B2,C1+C2))-1900
=MONTH(DATE(A1+A2,B1+B2,C1+C2))
=DAY(DATE(A1+A2,B1+B2,C1+C2))

But you get 4, not 5 for the day.

--
Kind regards,

Niek Otten

"an" wrote in message
...
Hello!

I have:

YY MM DD
2 9 20
4 3 15

I would like your help for the next result after sum periods:

YY MM DD
7 1 5

Thanks in advance.
an




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default SUM Days (to 30), Months (to 12) and Years

In this case. If your data were
2 9 20
4 5 15

you would get 7 for the day

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
=YEAR(DATE(A1+A2,B1+B2,C1+C2))-1900
=MONTH(DATE(A1+A2,B1+B2,C1+C2))
=DAY(DATE(A1+A2,B1+B2,C1+C2))

But you get 4, not 5 for the day.

--
Kind regards,

Niek Otten

"an" wrote in message
...
Hello!

I have:

YY MM DD
2 9 20
4 3 15

I would like your help for the next result after sum periods:

YY MM DD
7 1 5

Thanks in advance.
an






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
an
 
Posts: n/a
Default SUM Days (to 30), Months (to 12) and Years

Exactly, RG

Many thanks, more one time.
an

"Roger Govier" wrote:

Hi Just Extend the logic posted to your previous question on years and months.

=SUM(A2:A3)+INT(SUM(B2:B3)/12)
in Cell B4
=MOD(SUM(B2:B3),12)+INT(SUM(C2:C3)/30)
in cell C4
=MOD(SUM(C2:C3),30)

Note
This will not be accurate due to variation in month lengths.

Regards

Roger Govier


an wrote:
Hello!

I have:

YY MM DD
2 9 20
4 3 15

I would like your help for the next result after sum periods:

YY MM DD
7 1 5

Thanks in advance.
an





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
an
 
Posts: n/a
Default SUM Days (to 30), Months (to 12) and Years

Thanks, too.
an

"Niek Otten" wrote:

In this case. If your data were
2 9 20
4 5 15

you would get 7 for the day

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
=YEAR(DATE(A1+A2,B1+B2,C1+C2))-1900
=MONTH(DATE(A1+A2,B1+B2,C1+C2))
=DAY(DATE(A1+A2,B1+B2,C1+C2))

But you get 4, not 5 for the day.

--
Kind regards,

Niek Otten

"an" wrote in message
...
Hello!

I have:

YY MM DD
2 9 20
4 3 15

I would like your help for the next result after sum periods:

YY MM DD
7 1 5

Thanks in advance.
an







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
how do I convert a number to number of years, months and days because Excel Worksheet Functions 2 October 12th 05 06:15 PM
Function to calculate the number of years, months and days between Vicky Excel Worksheet Functions 2 July 15th 05 04:27 AM
Number of years, months, days between two dates. Bluenose Excel Worksheet Functions 34 June 30th 05 02:18 PM
Converting number of days to years and months Dave Cobb Excel Worksheet Functions 4 May 24th 05 09:14 PM
difference between two dates in years, months and days. ruby Excel Worksheet Functions 2 April 4th 05 04:51 PM


All times are GMT +1. The time now is 09:24 AM.

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"