two variable dates created from a single date

Me: total newbie, in a hurry. please tell me how, THANKS!

User enters a date in column A (m/d/yy).
Formula in column B needs to show column A date plus 60 days (m/d).
Formula in column C needs to show column A date, minus whatever # of days 
until 1st day of that month, then plus one year (m/1/yy).
e.g., 2/1/08 (column C) derived from user entry 2/17/07 (column A) 

0
lindsay (20)
8/2/2007 11:50:01 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
483 Views

Similar Articles

[PageSpeed] 47

in column B, try
=A1+60
formatted as m/d

in column C, try
=DATE(YEAR(A1)+1,MONTH(A1),1)


"Lindsay" wrote:

> Me: total newbie, in a hurry. please tell me how, THANKS!
> 
> User enters a date in column A (m/d/yy).
> Formula in column B needs to show column A date plus 60 days (m/d).
> Formula in column C needs to show column A date, minus whatever # of days 
> until 1st day of that month, then plus one year (m/1/yy).
> e.g., 2/1/08 (column C) derived from user entry 2/17/07 (column A) 
> 
0
jmb (270)
8/3/2007 3:32:03 AM
Works! You've been a life saver, ttthhhaaannnkkksss!!!

"JMB" wrote:

> in column B, try
> =A1+60
> formatted as m/d
> 
> in column C, try
> =DATE(YEAR(A1)+1,MONTH(A1),1)
> 
> 
> "Lindsay" wrote:
> 
> > Me: total newbie, in a hurry. please tell me how, THANKS!
> > 
> > User enters a date in column A (m/d/yy).
> > Formula in column B needs to show column A date plus 60 days (m/d).
> > Formula in column C needs to show column A date, minus whatever # of days 
> > until 1st day of that month, then plus one year (m/1/yy).
> > e.g., 2/1/08 (column C) derived from user entry 2/17/07 (column A) 
> > 
0
lindsay (20)
8/3/2007 4:44:00 PM
Quite welcome.

"Lindsay" wrote:

> Works! You've been a life saver, ttthhhaaannnkkksss!!!
> 
> "JMB" wrote:
> 
> > in column B, try
> > =A1+60
> > formatted as m/d
> > 
> > in column C, try
> > =DATE(YEAR(A1)+1,MONTH(A1),1)
> > 
> > 
> > "Lindsay" wrote:
> > 
> > > Me: total newbie, in a hurry. please tell me how, THANKS!
> > > 
> > > User enters a date in column A (m/d/yy).
> > > Formula in column B needs to show column A date plus 60 days (m/d).
> > > Formula in column C needs to show column A date, minus whatever # of days 
> > > until 1st day of that month, then plus one year (m/1/yy).
> > > e.g., 2/1/08 (column C) derived from user entry 2/17/07 (column A) 
> > > 
0
jmb (270)
8/3/2007 9:36:27 PM
Reply:

Similar Artilces:

Subtracting dates from years
hello I wonder if someone can help me. I need to deduct a number of years from a date. If you do cell - yrs ie: 24/07/2038 - 65 = 20/05/2038 but I need 24/07/2038 - 65 = 24/07/1973 When I use the Function Wizard this works =DATE(MONTH(A1), DAY(A1), YEAR(A1) -65) But does not display on the worksheet as so. always shows an incorrect date from 1909, This will work as an alternative, format the cell as date =DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)-65) With the date in A1: =date(year(a1)-65,month(a1),day(a1)) allison.rogers@friendsprov...

synchiing two computers?
Is there a way to synch Outlook on two computers? I end up with some email on my laptop and some on my desktop - it would be nice if the two could be synched so that I could have all of my email on both computers. Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Bill Andersen asked: | Is there a way to synch Outlook on two computers? | I end up with some...

Compare two cells in two different files and return answer
I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact mat...

selecting and inserting existing and non existing rows into table parameters and variables
I need to do the following: 1. Select all rows that exist both in the TVP @Headlines and in the table [Headlines].[Headlines] and insert them into a table variable @ExistingHeadlines. 2. Select the rows that exist only in the TVP @Headlines and not in [Headlines].[Headlines] and insert them into a table variable @NonexistentHeadlines. How would you do this? The stored procedure takes 2 totally different paths depending on whether or not the items in @Headlines already exist in the main Headlines table. 1. If they exist already, return an error telling the client that thes...

Dates displaying 1900 in chart when charting 1994 vs 2002
I have some very simple data that I need to compare in an XY scatter plot. There are about 10 data points (2 digits with one decimal) from 1994 and the same from 2002. When I select them and go to chart it, the dates display as 1900. I'm on Excel 2002 (10.5815.4219) sp-2. I did not find anything with a search of the knowledge base. This is for a presentation that happens end of this week to our village council. Arline - At first I thought it was #1, but as I think further, I've decided it's probably #2: 1. This might be the difference between the Mac calendar (which...

Pivot table for two different data variables
I have the following table Name type Cost overhead amy C1 23 12 bob C2 129 17 cat C2 36 45 doo C2 100 32 I owuld like to create a pivot table that looks similar to: the standard pivot table. (Please ignore type for now.) name Data Total amy Sum of overhead 200 Sum of cost 12 bob Sum of overhead 130 Sum of cost 23 cat Sum of overhead 90 Sum of cost 19 doo Sum of overhead 87 Sum of cost 34 Total Sum of overhead 507 Total Sum of cost 88 But with overall total (overhead + cost) for ...

Warn about doc dates outside open fiscal periods
I also had a similar problem under support incident #8416981 ------ Ok, let me explain further. The batch date was the current date. It was the document date on the transaction that it accepted. Put in a batch with a date within the fiscal year and within that batch put in a transaction with a date outside of the fiscal year. See if that gives you the error. The entry was input in January, I'll check with Andy tomorrow and see if he wants to actually change it. I just think Great Plains ought to be giving you a warning and it doesn't with the above. ------------- I had...

COUNTIF (?) on two criteria
I have a spreadsheet that logs work activities and records team members' completion of project units. Column B records team member names. Column C has project unit status - 'pending', 'beta' or 'completed'. It's easy to set up a formula to count how many units are allocated to each team member - =COUNTIF(B1:B1000,"Joe"). But what I need to do is set up formulas to count how many units Joe has completed, or has pending or at beta, and this means assessing data in both columns - which COUNTIF won't do. This doesn't strike me as an especially...

Create a custom font scheme
All I want to do is create my own font scheme. I use Publisher for all the publications I produce for my work and it would be wonderful to have the affiliate font scheme applied with a click of a button. So, how do I create my font scheme? I already did the custom color scheme, but I can’t figure out how to do the font scheme. Please help! ...

Two IF Statements with two answers
In cell J9 I need a formula that would do the following: If G9="Yes" then F9, If G9="No" then -f9 Note: G9 has a drop down list with Yes, No answers. If there are no other alternatives that yes and no =IF(G9="Yes",F9,-F9) otherwise =IF(G9="Yes",F9,IF(G9="No",-F9,"") Regards, Peo Sjoblom "heater" wrote: > In cell J9 I need a formula that would do the following: > > If G9="Yes" then F9, If G9="No" then -f9 > > Note: G9 has a drop down list with Yes, No answers. > =IF(G9=&qu...

QIF 2003 into 2008
Hi everyone. You'll see in an extended earlier thread that I've had no luck upgrading from 2003 Deluxe to 2008 Plus Deluxe. To my great frustration, I have no problems moving from Money 2003 to Quicken 2008... grrr.... One thing I'm trying as a last ditch effort is to export the individual accounts from Money 2003 as QIF files. I can import them into Money 2008, but about half of all the account entries are missing dates. To us, that renders them effectively useless. Anyone seen this before? Any idea why? Any info I can provide that might clarify this? ...

select date from list
Hello, I am working on a time database for employees to enter time. I have 3 tables, one with employee names, one with all the days of the year, and one where the time info gets entered. The tables look similar to the following with the employee and date table linked by employee id and the date table and work table are linked by date id. Employee Table Date Table Work Table Employee Id Date ID Work ID Employee Name Date Project Pay Period Time Employee ID Date ID In the fi...

Two axis
Positioning the Vertical axes: I have a chart where there are two axes. The first implementation produced the two scales on the right side, one inside, the other outside the chart area. Unusual, but luckily it fits well the chart. I tried to duplicate the feat with another dataset (similar in structure) and did not even get the second scale to show right, left or center (data plotted with two different scales allright). Where is the info to control these functionalities? Tx, Pierre On Wed, 29 Oct 2003 17:48:13 +0100, pl.carry <pl.carry@wanadoo.fr> wrote= : > Positioning the Vertic...

Variables in WHERE clause
I am using SQL Server 2005 and the T-SQL below to select records. If I type in the WHERE clause values (as shown) the query time is 1 second or less. If I use the variables @YearStart and @EndDate in the WHERE clause the query takes 33 seconds. Can someone explain and give me an alternative? Thanks. DECLARE @StartDate datetime; DECLARE @EndDate datetime; DECLARE @YearStart datetime; DECLARE @ItemId varchar(24); SET @StartDate = '8/1/2010'; SET @EndDate = '8/31/2010'; SET @YearStart = '1/1/2010'; SET @ItemId = null SELECT D.PartId, MIN(I.Desc...

plot two groups of numbers on one series of xy coordinates.
how to create a plot from separate x and y values. i.e. y values do not correspond to the same set of x values. example x1= 5, y1=10; x2= 8, y2=15 I can read your question 2 ways; A) you data looks like this x y 5 10 8 15 etc. Select the data and make an XY chart B) you have two sets of data x1 y1 5 10 10 12 17 15 21 20 and x2 y2 3 8 7 14 12 19 18 24 Make an XY chart of the first data series Select all the data (including headers) for second set, and use Copy Click the chart to activate it Use Edit | Paste Special .... indicate New Ser...

two variables
I have a query that asks for collcode, collcode2 and booth. On any given day an employee may be collcode or collcode2. I would like to know how many times a certain emp has been paired with another employee. So how do I ask if an employee has been collcode/collcode2 and who the other person was? I hope this question makes sense. Thanks for any help Query one: SELECT CollCode, CollCode2, Booth FROM YourTable UNION ALL SELECT CollCode2, CollCode, Booth FROM YourTable Query Two SELECT CollCode, CollCode2, Count(CollCode) AS TIMESPAIRED FROM QueryOne GROUP BY CollCode, CollCode2 IF y...

Find two PCI instance but load Init() one time
Hi all, I'm developing a stream interface driver for my PCI device under WinCE 6.0 and this driver works very well currently. Now, I got a problem when testing two the same PCI devices with my stream driver. The registry can find two PCI instances (i.e. PCI\Instance\mydrv1, PCI\ Instance\mydrv2) but system only call my XXX_init() one time. Does anyone know what the problem is? The following information is my registry setting: ================================================================== [HKEY_LOCAL_MACHINE\Drivers\BuiltIn\PCI\Template\mydrv] "Dll"="myd...

Date Format
User has a spreadsheet with dates - It used to be formated as Month-day (Mar-01). "All of a sudden" the column now displays another date format - example 38569 - in the cell, in the formula field it shows the correct day, but as mm/dd/yyyy. The format of the column is date. We looked at another formula - example ="01/01/1900"+38569 and it shows in the formula field as the correct date - so this is the count of days from 01/01/1900 - how do we convert it back? BTW we copied the entire spreadsheet to a new one - and the date displays correctly! -- Rona/tinkertoy...

importing dates and tasks from excel to outlook
hi :) is it possible to import tasks from excel to outlooks calendar, so i dont have to do it one by one in outlook, but all at once by importing from excel? Sure. What happens when you try? Hint: export from Outlook so you can get the field names to use, then you won't have to map fields before importing and save the excel sheet as CSV so you don't have to name the range. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-...

Comparing two lists for matches
Hi, I am trying to do the following: There are two sets of supplier lists - A List and B List. I need to compare the two lists. Source A is my master. Source B is a subset of that but has contact information for all the suppliers. Hence, my goal is to retrieve the contact information from Source B and match it to the suppliers of Source A. The above can be easliy achieved using a vlookup funtion. But the problem is that some of the supplier names in the two lists are not exact matches. Hence, vlookup does not catch it. For instance, a supplier is listed as ABC Inc. in A list and as ABC, inc....

Compare two lists of names
Can you compare two lists of names where one list is last name first and the other is first name last? try this formual: =RIGHT(TRIM(E2),LEN(E2)-FIND(" ",E2))&" "&TRIM(LEFT(TRIM(E2),FIND(" ",E2))) it will convert First name last name to last name, first name or Visa versa. then you can compare your lists. "ea" wrote: > Can you compare two lists of names where one list is last name first and the > other is first name last? ...

Two copies of all Mailboxes
Using my sbs2003 srver with exchange 2003 installed - I notice two copies of all users mail box in the list of mailboxes using outlook 2003 on the same server. When I try to close these I get an error syaing mail boxes in use. I cn not remove them. Can you tell me how to remove these so I have just on mail box visible per user? thx Kevin Cain Class TECHNOLOGY ...

How do I create a chart on two axes with two lines and a bar
I am trying to compare two final stock prices to the average volume. So, teh left axes would have the numbers for the stock prices, and the right axes would have the volume. The two stock prices would be lines using the left, and the volume a bar using the right. I can do one line and one bar, but I can't figure out two lines. Can anyone help? Forget the built in custom types. It's better to build your own. Create your chart using all the data, to produce a chart with three line series all on the primary axis. Select the Volume series, and on the Chart menu, use Chart Type to ch...

Same operations on a variable number of rows
I am posting this to both the regular and the programming group because I don't know if the solution requires programming. If not, sorry and ignore this. Anyway, here is my questions. I have a specific and then a more general issues. I have a set of data that changes frequently. In particular, the number of items change. What I want to do is get the list of unique concatenations of the items. Now that part is easy. I concatenate the cells. Copy as value. Then make a filter unique. But that is lots of action on my part. I have to copy the concatenate formula to a variable number of rows. I...

*Keeping* multiple CToolbars docked in a single row during window resize
I have an MDI app with several toolbars docked in a single row (using DockControlBarLeftOf etc.) I am trying to prevent the rightmost toolbars from repositioning beneath the other toolbars in the same row as the main window frame is resized. Currently each toolbar is automatically repositioned on the left of the screen in order to prevent it going out of view as the window width shrinks. I would like to keep the toolbars as they are initially set; in a single row, during any window sizing operations. I still need to make the toolbars *manually* draggable, floatable and moveable by user mou...