+ Reply to Thread
Results 1 to 5 of 5

What formula will repeat the same date 31 times down, skip one cell, add 1 day and repeat?

  1. #1
    Registered User
    Join Date
    03-08-2014
    Location
    Tucson Arizona
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question What formula will repeat the same date 31 times down, skip one cell, add 1 day and repeat?

    I created a spreadsheet for use as a personal time log but the trouble is that I cannot figure out how to create a formula or macro to accomplish my objective here.

    I searched the forum and found a variety of answers that are semi related but aren't quite the same as what I need. I haven't been able to figure out how to modify a previously posted formula to meet my needs either. I also did some research into offset, the Dim command and arrays and various macro tutorials without any success whatsoever.

    Attached is the spreadsheet but for anyone who does not want to open it:

    Cell A1 contains the column header "Date"
    Cells A2-A32 contain 03/08/2014
    Cell A33 contains the column header "Date"
    Cell A34 should contain 03/09/2014
    Cells A34-A64 should contain 03/09/2014
    Cell A65 contains the column header "Date"
    Etc...

    The ideal solution would either apply this pattern to cover a 1 year range or allow it to be applied to as many cells as desired.

    Help would be greatly appreciated.

    Time Log.xlsx

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: What formula will repeat the same date 31 times down, skip one cell, add 1 day and rep

    Hi,

    In A3 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: What formula will repeat the same date 31 times down, skip one cell, add 1 day and rep

    (Didn't refreshed and posted. Unintended cross posting)

    Or you can use in A34
    =IF(MOD(ROW($A33),32)>0,DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)),"Date") and drag down till your data ends
    Last edited by hemesh; 03-08-2014 at 07:58 AM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Registered User
    Join Date
    03-08-2014
    Location
    Tucson Arizona
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: What formula will repeat the same date 31 times down, skip one cell, add 1 day and rep

    How did you guys learn this stuff?? -.-

    Thank you for the hilariously fast replies, looks like I wasted about 3 hours trying to figure this out on my own.

    Hemesh, I actually needed the day incremented, not the month, rep added anyway since it does work and I can probably modify this for another use.

    I used the solution provided by Richard this time, thanks a ton. Works perfectly.

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: What formula will repeat the same date 31 times down, skip one cell, add 1 day and rep

    in that case you could
    =IF(MOD(ROW($A33),32)>0,DATE(YEAR(A2),MONTH(A2),DAY(A2)+1),"Date") and drag down till your data ends

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Repeat a formula 5 times before changing cell reference
    By amartin575 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-28-2014, 04:11 PM
  2. Automatically repeat four times with a formula vertically
    By Enda80 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-05-2014, 11:47 AM
  3. Replies: 1
    Last Post: 04-04-2013, 05:29 PM
  4. [SOLVED] VBA: repeat a formula a number of times
    By dinahajjar in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-24-2013, 11:03 AM
  5. repeat something n times formula
    By cro4you.org in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2008, 11:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1