sequential date

Is it possible to have in sequence a date value

I require four dates the same before sequencing the next date, so

01/01/11
01/01/11
01/01/11
01/01/11
02/01/11
02/01/11
02/01/11
02/01/11
03/01/11
03/01/11
03/01/11
03/01/11

etc etc

Cheers 


0
10/27/2011 9:58:05 AM
excel 39879 articles. 2 followers. Follow

4 Replies
772 Views

Similar Articles

[PageSpeed] 55

In A1 : initial date
In A2 :
=IF(COUNTIF($A$1:A1,A1)<4,A1,A1+1)
copy down as needed.
Daniel


> Is it possible to have in sequence a date value
>
> I require four dates the same before sequencing the next date, so
>
> 01/01/11
> 01/01/11
> 01/01/11
> 01/01/11
> 02/01/11
> 02/01/11
> 02/01/11
> 02/01/11
> 03/01/11
> 03/01/11
> 03/01/11
> 03/01/11
>
> etc etc
>
> Cheers


0
10/27/2011 10:16:07 AM
On Oct 27, 5:16=A0am, DanielCo <dcolardelle...@free.fr> wrote:
> In A1 : initial date
> In A2 :
> =3DIF(COUNTIF($A$1:A1,A1)<4,A1,A1+1)
> copy down as needed.
> Daniel
>
>
>
>
>
>
>
> > Is it possible to have in sequence a date value
>
> > I require four dates the same before sequencing the next date, so
>
> > 01/01/11
> > 01/01/11
> > 01/01/11
> > 01/01/11
> > 02/01/11
> > 02/01/11
> > 02/01/11
> > 02/01/11
> > 03/01/11
> > 03/01/11
> > 03/01/11
> > 03/01/11
>
> > etc etc
>
> > Cheers

Another idea withOUT formulas to start with today and increase as
desired
Sub doDates()
fn =3D Date
For i =3D 1 To 120 Step 4
Cells(i, 1).Resize(4) =3D fn
fn =3D fn + 1
Next i
End Sub



0
dguillett11 (103)
10/27/2011 12:11:17 PM
"Garry" <redwingdirect@btconnect.com> wrote:
 > I require four dates the same before sequencing
> the next date, so 01/01/11
> 01/01/11
> 01/01/11
> 01/01/11
> 02/01/11
> 02/01/11
> 02/01/11
> 02/01/11
[....]

Put the initial date into A1, put =A1 into A2 and copy down through A4. 
Then put =A1+1 into A5 and copy down as needed 

0
joeu20041 (141)
10/27/2011 4:00:12 PM
On Thu, 27 Oct 2011 10:58:05 +0100, Garry wrote in
microsoft.public.excel:

>Is it possible to have in sequence a date value
>
>I require four dates the same before sequencing the next date, so
>
>01/01/11
>01/01/11
>01/01/11
>01/01/11
>02/01/11
>02/01/11
>02/01/11
>02/01/11
>03/01/11
>03/01/11
>03/01/11
>03/01/11

A1: 1-Jan-2011
A2: =A1+.25
Copy A2 down.

May have unintended consequences.

-- 
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"
0
10/28/2011 9:47:42 AM
Reply:

Similar Artilces:

grouping dates by week/month/etc. on cat. axis
I just want to group my dates in a pivotchart by months or weeks to get sales or weight sums by the month or week. Problem is that the chart won't allow me to group the dates--it says that this type of data cannot be grouped. why? why? why? the dates in the cells also have time info: i.e. 7/1/2004 9:53:07 AM so when I put the Date field into the Category area of the chart it plots every row down to the second! I'm lost and confused and searched the whole newsgroup but can't find any postings of this same issue. it seems like something common. can anyone help? thanks, kamal ...

Sequential Numbering
I am trying to automatically print sequential numbers on individual cards on 8.5" x 11" business card stock (to create tickets) using MS Publisher and cannot figure out how to get it to do this. Does anyone know the secret? Thanks. Hi Scott (scottmarsh@excite.com), in the newsgroups you posted: || I am trying to automatically print sequential numbers on individual || cards on 8.5" x 11" business card stock (to create tickets) using MS || Publisher and cannot figure out how to get it to do this. Does || anyone know the secret? Thanks. Check out the following tutorial:...

Multiple rows, same date, multiple start/end times... how might I...
I have a workbook that has a sheet with: multiple consecutive rows each have the same date. then the date changes for another set of consecutive rows like this (pastes excel cells below - hope you can see it?). Date Start Time End Time Daily Total Running Total Friday, August 01, 2003 8:00 AM 11:00 AM 3.00 3.00 Friday, August 01, 2003 11:00 AM 11:30 AM 0.50 3.50 Friday, August 01, 2003 12:00 PM 2:15 PM 2.25 5.75 Friday, August 01, 2003 2:15 PM 4:30 PM 2.25 8.00 Friday, August 01, 2003 4:30 PM 6:00 PM 1.50 9.50 Wednesday, August 06, 2003 8:00 AM 9:00 ...

Auto formula when putting in date
I have a workbook where, when I enter a date such as 11/30, Excel automatically puts an = in front of it, yielding a value I don't want. I have several sheets in the workbook and they all do the same thing. The only way I can get the cell to look like a date is to put a "'" in front of it and then it doesn't act like a date (i.e., I can't use it in a different formula to compare the date). Other workbooks will automatically format the cell as a date and do not put the = sign in front. I can put a date in the other workbook, paste it into the cell in the fi...

Cannot set task start or end date!!!
For some reason I can no longer set up task start or end dates. When I open the drop down list, it will not stay open when I release the left click. I can hold down left click and choose a date, but if I want to choose the "today" or "none" radio buttons, that will not work. Any ideas?? I am also using the Xobni program with Outlook, but do not have this problem with the same config. at home. I can set the status, priority and % complete fields with absolutely no problem. Thanks Howard Does it work in Outlook Safe Mode? Start-> Run; outlook.ex...

Yesterday's Date
I'm trying to pull data for today or yesterday's date. I'm using the query below to pull todays date, but would like to add an OR statement for yesterday. SELECT LICENSE_DATETIME FROM chatt_INVOICE_ITEM_TEMP WHERE license_datetime = (SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]) The above statement is working. I tried running the statement with a -1 instead of , but it's invalid. What the correct way to do this? Thanks!! Angie The best is to use a half-open interval and the date/time functions to set the time at midnight. See the f...

count dates
Hi How do I count then rows where the date is within a certin month ? eg: A B C 1 xxx some text 01-01-2005 2 yyy some text 31-01-2005 3 zzz some text 01-02-2005 4 vvv some text 01-03-2005 6 Number of rows where month is 01 (2) 7 Number of rows where month is 02 (1) 8 Number of rows where month is 03 (1) Thanks in advance. Best Steen One way would be =SUMPRODUCT(--(MONTH(C1:C4)=1)) for January You could put month numbers 1 - 12 in cells D1:D12 then enter in E1 =SUMPRODUCT(--(MONTH($C$1:$C$4)=D1)) then copy down through E2:E12 Change...

Receipt / Invoice printing Terms & Due date
Good day, We do a lot of charging on customer accounts. Is there a way to show the Terms and the Invoice due date on each invoice / receipt? Thanks in advance, Steve The only way I have been able to accomplish this is by putting the terms into a customer custom text field and printing that on the receipt. We then created a customization that will populate a receipt variable with the current date + the number of days in the terms. I have not been able to use any other receipt variable to get this to show up. Casey Hanson New West Technologies Check out amazing RMS extensions: http:...

How to create a date mask and drop down list?
I am not sure if this is the proper wording, but is their anyway t create a standard date function/mark, to type dates in without havin to type DASH, having to type out the entire year etc.... Also how can I create a drop downlist to choose from (that show results when I type the first letter or second letter) in a column? Any help with this would be greatly appreciated thanks -Niko -- Niko ----------------------------------------------------------------------- Nikos's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=986 View this thread: http://www.excelforum.c...

Current Date Marker
I have a calculated chart of price vs date and would like to create a vertical line indicating current date so that the progress can be easily checked. Have been unable to find out how to create a vertical pole. Can someone help? Hi have a look at http://andypope.info/charts/averageline.htm http://www.tushar-mehta.com/excel/charts/straight_lines/index.html http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine -- Regards Frank Kabel Frankfurt, Germany Dave wrote: > I have a calculated chart of price vs date and would like > to create a vertical line indicating current date...

After Up-date
This is probably a Access 101 question, but here goes! I have an issue with my form that when a user updates a field, and then goes to navigate away to a different record using a drop down, we get a error message. I know what it is, the entry has not, been saved, the record is still in edit mode. Is there a common practice that can eliminate this, and after-up-date command or something? Up-dating any sub form fields work fine, just fields on the parent table / form. Any help is appreciated. CB On Thu, 4 Feb 2010 06:33:01 -0800, CB <CB@discussions.microsoft.com> ...

Controlling Sequential Numbers
I have been researching using sequential numbers for invoices an purchase orders, but all of them seem to advance the number eithe every time the document is opened or saved. My purchase order is par of a larger file, and since I will be opening and saving the fil without necessarily writing a purchase order I need more control ove its advancement. I was wondering how I could create a button that whe pressed or selected would advance the number by 1? Also, how could make it create a unique number depending on who's creating the purchas order. For example, let's say the user is ...

Date problem
Need help. Having problem to bring the date into string. Currently, I have to declare @cp_dt in sql string where @cp_dt is within the quote. I dont want to re-declare @cp_dt (double work). Is there any other way to do the following :- declare @cp_dt datetime, @cp_dt_cv varchar(10), @cp_dt_txt varchar(8), @cp_file varchar(6) set @cp_dt = (select top 1 position_date from glob.dbo.cp order by position_date desc) set @cp_dt_cv = convert(varchar(10),@cp_dt,103) set @cp_dt_txt = replace(@cp_dt_cv,'/','') set @cp_file = (select right(lt...

how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

Bizarre Excel Copy/Paste Date Issue
I'm working on 2 xls Excel 2000 files (with all Office Updates and Windows Updates applied on this WinXP PC). I'm copying a date from a cell in A.XLS and pasting it into B.XLS. Both cells are formatted as dates. I copy the 1/1/1999 from A.XLS but when I paste it into B.XLS, it shows up as 1/1/1994. There are NO formulas involved and I have auto-calculation ON. They are not text, they are true date entries. I've never seen anything like this and wouldn't know where to look for help on this except for here. Any suggestions greatly appreciated. -Rob Rob, One work...

Date Format #8
I have a column of dates in the format xx.yy.zzzz. I want them to be displayed as the format xx/yy/zzzz. The cell format custom option does not convert the date from one to the other. How can I do this? "Ron Cardy" <anonymous@discussions.microsoft.com> wrote in message news:3FBF5040-4011-4966-8FBE-F22F70CF63B6@microsoft.com... > I have a column of dates in the format xx.yy.zzzz. I want them to be displayed as the format xx/yy/zzzz. The cell format custom option does not convert the date from one to the other. How can I do this? At the moment, you actually have text strings...

Next sequential number on a different form
Is there a way to do this: I have a form called "List of Proposals Written" that is listed by th Proposal number. Is there a way to display on another excel spreadsheet, the mos current Proposal number? I know Access you use something like Dmax(ProposalID)+1 What should I do in Excel:confused: Any help would be greatly appreciated! Michae -- Message posted from http://www.ExcelForum.com "daniels012 >" <<daniels012.1b8pra@excelforum-nospam.com> wrote > I have a form called "List of Proposals Written" that is listed by the > Propos...

Problems with date and time on Notes.
I have just started using Outlook Notes. I note (no pun intended) that the date and time on some Notes updates to the current date and time each time I open it. How can I fix things such that the date and time remains set at the date and time the Note was created? Got it. Do it in the code, don't try to do it in FP. "Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message news:%23XUJeVoBIHA.5752@TK2MSFTNGP02.phx.gbl... >I have just started using Outlook Notes. I note (no pun intended) that the >date and time on some Notes updates to the ...

Is there any way to organize this forum by date?
Hi. My listing for this forum (All threads) shows the last three responses (at the top) as variously 4/30/2006, 11/4/2009 and then 1/14/2004. Entries for 2010 come further down. Is there a way to organize this more helpfully? Thanks, Ian chromian wrote: > Hi. My listing for this forum (All threads) shows the last three responses > (at the top) as variously 4/30/2006, 11/4/2009 and then 1/14/2004. > > Entries for 2010 come further down. > > Is there a way to organize this more helpfully? > > Thanks, > > Ian Yes but you need to cl...

Find missing sequential numbers
Hello, I'm looking for a way to quickly find what numbers are missing in column B. I can sort them ascending, but how do I find if there are missing numbers? 1 2 3 5 6 7 9 I need to know 4 and 8 are missing. Thank you. One way: select B2:Bx. Choose Format/Conditional Formatting... CF1: Formula is =(B2-B1)>1 Format1: <pattern>/<some color> or, without sorting, select column B (with B1 active): CF1: Formula is =AND(B1>MIN(B:B),COUNTIF(B:B,B1-1)=0) Both CF's will activate if there are missing numbers before them. In article <28706E9E-2624...

Date Wont Display As Date
Greetings, This is a simple one, I hope. Here is the problem formula: ="Service Date"&CHAR(10)&Enter!AI4 I am trying to get this formula to show as: Service Date 01/15/04 Instead I get: Service Date 38001 Any ideas on how to get the date to show and not the date code? TIA -Steve Moulton Minitman, Format/Cell/Date Pick one of your liking. John "Minitman" <excelreply@i-m-p.net> wrote in message news:n40e00hgkdm1a5hlq9neq5echhoqu6htca@4ax.com... > Greetings, > > This is a simple one, I hope. > > Here is the problem formula: > >...

Date Add
Hey all, Im trying to do somthing that I am guessing is quite simple. Im working on a Db that tracks a fleet of rental skiis. Every 2 years these skis must be tested. I have a table that tracks all the information about a given pair of skis, including the date they were last tested. What I am trying to do is have a feild within that table autocomplete using the [test date] feild, plus 2 years. This new feild would then become the date when the next test was required. The Table is called [Ski Info] The first date feild is [Test date] the feild I would like to have calculated is [Next te...

future dates in payables
Hello: An end user accidentally entered payables transactions in future dates such as 2229. We do not want to have to create fiscal periods for years so far in the future. But, if we have to, we will. Anyway, how would we void or get rid of transactions accidentally entered with future periods? Thanks! childofthe1980s Why can't you just delete the transaction? You can't post to a period that hasn't been setup. Frank Hamelly MCP-GP, MCT East Coast Dynamics ww.eastcoast-dynamics.com The client says that there are some transactions in the system like that that she cannot...

X axis date
Hi I'm using Excel '97 (Yeah, I know, but it's all I can afford at the moment) I want the X axis to always display a month beyond the latest date, so that the line graph stops short of the right hand side. I can get the Y Axis of value to stay ahead of the game by a consistent amount, but the date defaults back to the latest date when Auto for Max value is ticked. Is this possible? Will I have to use VBA? Please clue me in. Thanks Dave F. Add a series to the chart. It can consist of a single point, where the date is calculated as the maximum in your date column plus 30, an...

Date Increase
I currently have a form for entering current passwords for systems. I have a field to enter the date the password was last changed, and another field for the date the password expires, which is every 30 days. Is there anyway i can set the date password expires field to update automatically? The 2nd field is just calculated from the 1st and better left as a calculation in a query rather than a field in a table. There are no triggers at the table level in Access. Dan Wood wrote: >I currently have a form for entering current passwords for systems. I have a >field to enter...