Working With Dates in Excel
 

Hello:

I noticed that when I subtract one date from another in Excel it treats them
as integers. For example if I subtract 10-1 in Excel it = 9. With the dates
if I subtract 1/31/2010 - 1/1/2010 it will = 30 in Excel because Excel see
that is 31-1=30.

If I am using Excel as a log to keep up with what I doing for example I
started this project on 1/1/2010 and the final day I worked on the project
was on 2/16/2010. So to keep up with what I am doing I entered 1/1/2010 on an
Excel worksheet at the beginning of the first day then at the end of the last
day I enter I entered 2/16/2010. In this example its pretty easy to see that
I worked 47 days on this project but lets say I had starts and stops working
on other things intermittently and the project ran months not starting or
stopping on nice even days and I wanted to subtract dates as I have them
entered in my workbook to give me a running total of days I have worked on
the project what would happen is every time I subtracted on date from another
Excel would short me one day. If I didn't know this my information could
short change me or bring my creditability into question. Is there way to do
what I am trying to do that I do not know about?

Working With Dates in Excel
 

I would suggest convert your date into Julian Date in one cell, in another
cell enter the number of days you want to add to the Julian Date and then
convert it back into the standard date.

Standard Date to Julian Date: =RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),
"000")
Julian to Standard: =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT
(A1,3))
Adding days into Julian Date: =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2)
,1,RIGHT(A1,3)+A2)

Working With Dates in Excel wrote:
Hello:

I noticed that when I subtract one date from another in Excel it treats them
as integers. For example if I subtract 10-1 in Excel it = 9. With the dates
if I subtract 1/31/2010 - 1/1/2010 it will = 30 in Excel because Excel see
that is 31-1=30.

If I am using Excel as a log to keep up with what I doing for example I
started this project on 1/1/2010 and the final day I worked on the project
was on 2/16/2010. So to keep up with what I am doing I entered 1/1/2010 on an
Excel worksheet at the beginning of the first day then at the end of the last
day I enter I entered 2/16/2010. In this example its pretty easy to see that
I worked 47 days on this project but lets say I had starts and stops working
on other things intermittently and the project ran months not starting or
stopping on nice even days and I wanted to subtract dates as I have them
entered in my workbook to give me a running total of days I have worked on
the project what would happen is every time I subtracted on date from another
Excel would short me one day. If I didn't know this my information could
short change me or bring my creditability into question. Is there way to do
what I am trying to do that I do not know about?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/201005/1

Working With Dates in Excel
 

Add1

=B1-A1+1

Its worth reading this section on how excel stores the dates..by Chip Pearson
http://www.cpearson.com/excel/datetime.htm

--
Jacob (MVP - Excel)


"Working With Dates in Excel" wrote:

Hello:

I noticed that when I subtract one date from another in Excel it treats them
as integers. For example if I subtract 10-1 in Excel it = 9. With the dates
if I subtract 1/31/2010 - 1/1/2010 it will = 30 in Excel because Excel see
that is 31-1=30.

If I am using Excel as a log to keep up with what I doing for example I
started this project on 1/1/2010 and the final day I worked on the project
was on 2/16/2010. So to keep up with what I am doing I entered 1/1/2010 on an
Excel worksheet at the beginning of the first day then at the end of the last
day I enter I entered 2/16/2010. In this example its pretty easy to see that
I worked 47 days on this project but lets say I had starts and stops working
on other things intermittently and the project ran months not starting or
stopping on nice even days and I wanted to subtract dates as I have them
entered in my workbook to give me a running total of days I have worked on
the project what would happen is every time I subtracted on date from another
Excel would short me one day. If I didn't know this my information could
short change me or bring my creditability into question. Is there way to do
what I am trying to do that I do not know about?

Working With Dates in Excel
 

Add 1

=B1-A1+1

Its worth reading this section on how excel stores the dates..by Chip Pearson
http://www.cpearson.com/excel/datetime.htm

--
Jacob (MVP - Excel)


"Working With Dates in Excel" wrote:

Hello:

I noticed that when I subtract one date from another in Excel it treats them
as integers. For example if I subtract 10-1 in Excel it = 9. With the dates
if I subtract 1/31/2010 - 1/1/2010 it will = 30 in Excel because Excel see
that is 31-1=30.

If I am using Excel as a log to keep up with what I doing for example I
started this project on 1/1/2010 and the final day I worked on the project
was on 2/16/2010. So to keep up with what I am doing I entered 1/1/2010 on an
Excel worksheet at the beginning of the first day then at the end of the last
day I enter I entered 2/16/2010. In this example its pretty easy to see that
I worked 47 days on this project but lets say I had starts and stops working
on other things intermittently and the project ran months not starting or
stopping on nice even days and I wanted to subtract dates as I have them
entered in my workbook to give me a running total of days I have worked on
the project what would happen is every time I subtracted on date from another
Excel would short me one day. If I didn't know this my information could
short change me or bring my creditability into question. Is there way to do
what I am trying to do that I do not know about?