ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   calculating dates (https://www.excelbanter.com/excel-discussion-misc-queries/230753-calculating-dates.html)

future spreadsheet master

calculating dates
 
I'm wondering if I am able to calculate a date based on two different cells.
A1 is my actual start date 6/28/2006
C1 is the current date (ex 5/13/2009)
B1- I want this is be the start date for the person's year at work. so I
want it to calculate 6/28/2008. The catch is I can't just go -1 on the year
because when the current date is 7/1/2009 (C1) I need B1 to be 6/28/2009 to
relect only being 3 days into that work year.
Is this possible? If so, how?

Sheeloo

calculating dates
 
Try this in B1
=C1-MOD(C1-A1,365.25)+1

"future spreadsheet master" wrote:

I'm wondering if I am able to calculate a date based on two different cells.
A1 is my actual start date 6/28/2006
C1 is the current date (ex 5/13/2009)
B1- I want this is be the start date for the person's year at work. so I
want it to calculate 6/28/2008. The catch is I can't just go -1 on the year
because when the current date is 7/1/2009 (C1) I need B1 to be 6/28/2009 to
relect only being 3 days into that work year.
Is this possible? If so, how?


Roger Govier[_3_]

calculating dates
 
Hi

In B1
=A1-DATE(YEAR(C1),6,28)

--
Regards
Roger Govier

"future spreadsheet master" <future spreadsheet
wrote in message
...
I'm wondering if I am able to calculate a date based on two different
cells.
A1 is my actual start date 6/28/2006
C1 is the current date (ex 5/13/2009)
B1- I want this is be the start date for the person's year at work. so I
want it to calculate 6/28/2008. The catch is I can't just go -1 on the
year
because when the current date is 7/1/2009 (C1) I need B1 to be 6/28/2009
to
relect only being 3 days into that work year.
Is this possible? If so, how?



smartin

calculating dates
 
Sheeloo wrote:
Try this in B1
=C1-MOD(C1-A1,365.25)+1


That will be off by one day in many circumstances. Try it with A1 =
2004/03/01 and today's date in C1.


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com