Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

increment date by one day

Hello,

I'm trying to show a date in MM/DD format going across five cells that represent the days of the week. I want to calculate the correct date after entering a start date. For example:

I would modify the formula in cell B6 using the DATEVALUE formula by typing in a specific start date, such as:=DATEVALUE("6/30/2008")

I would then like cells E11:I11 to show:

E11: Mon,6/30
F11: Tues,7/1
G11: Wed,7/2
H11: Thurs,7/3
I11: Fri,7/4

I chose to use the DATEVALUE function because I read that it's easier to add dates when converted into their serial number equivalent. I would also like to do this without VBA, if possible. If I'm attempting this all wrong, feel free to point me to the right direction.

Appreciate the help!

Emil


Post your answer or comment

comments powered by Disqus
My apologies in advance if part of this question is off topic.

How do I use vba to increment a date by one month ....

and (probably off topic)

What is the usual practise for dating 'monthly' invoices when the date of
previous invoice falls in the range 29th to 31st and the following month has
less days

Regards & TIA

Hi,

I intend to use a plugin for WordPress that takes data from Excel and inserts it into WP to create posts. The columns include the post, the post title and a timestamp (date)

I have at least a year's worth of posts :-) which I want to upload

I need to be able to take a starting date and go backwards for a year, simply to save me manually typing out each date.

The format of the starting date is Mar 19, 2008 16:54:11 PM

The minutes don't matter - they're just there to make the plugin work

What I want to do though is go back through the months, with each month being accurate i.e. if there are 30 days in one month, and 31 in another, that's got to be accurate.

If I try and do this manually it's going to be mind-numbing.

I've followed the instructions in Excel 2000 without any success.

Create linear and growth trends with the Series command
When you use the Series command, the resulting series replaces the original selected values. To save the original values, copy them to a different row or column. Then create the series by selecting the copied values.

Select the cell where you want to start the series. The cell must contain the first value in the series.

On the Edit menu, point to Fill, and then click Series.

Do one of the following:
To fill the series down the page, click Columns.

To fill the series across the page, click Rows.

In the Step value box, enter the value that you want to increase the series by. In a linear series, the step value is added to the first starting value and then added to each subsequent value. In a growth series, the first starting value is multiplied by the step value. The resulting product and each subsequent product is then multiplied by the step value.

Under Type, click Linear or Growth.

In the Stop value box, enter the value you want to stop the series at.
Note If there is more than one starting value in the series and you want Microsoft Excel to generate the trend, select the Trend box. In a linear series, the starting values are applied to the least-squares algorithm (y=mx+b) to generate the series. In a growth series, the starting values are applied to the exponential curve algorithm (y=b*m^x) to generate the series. In either case, the step value is ignored. The series created is equivalent to the values returned by the TREND function or the GROWTH function. I've also tried creating a starting date, and then a date one day less above it, and dragging the corner handle up the page, but all I get is a list of identical dates, even when I right click on this and try and see if there are any options.

Any help would be greatly appreciated.

I need some helping in updating dates within Excel. I need to update the dates by seven days but then when it comes to the end of the month, I need to add only one day to start off the new month etc.

I've attached my excel file for reference. In the end, I want to add a button as I do for creating a PDF so I can update the dates with a click of a button.

Any help would be appreciated.

Thanks!

Hi, I've got a userform with a listbox which contains a number of date range in the format "dd/mm/yy-dd/mm/yy". I want to take this range, and add each date within it to an array, so that I can then search through a column against this array, and check to see if the date in each row is within one of the specified ranges.

I think I'm nearly there, but I can't work out how to increment the date by one day!

Here's my code, it's failing on the line StartDte = StartDte + 1

Code:
StartDte = Left(TempWkString, InStr(TempWkString, "-") - 1)
        EndDte = Right(TempWkString, Len(TempWkString) - InStr(TempWkString, "-"))
        If IsDate(StartDte) = False Then GoTo Errhandler
        If IsDate(EndDte) = False Then GoTo Errhandler
        Do While StartDte < EndDte
            DateSearcher(iny, DateNo) = DateI
            DateNo = DateNo + 1
            StartDte = StartDte + 1
            ReDim Preserve DateSearcher(0 To DateSearchList, 0 To DateNo + 1)
        Loop
Cheers

Charlie

I am using MS Excel 2003

I have asked this question before : http://www.excelforum.com/excel-new-...-and-over.html at the time I responded but had not actually tried the suggestion. Next day I was moved in my department so I never got the chance to try the sugestion. Now I am back and being asked once again to see if I can clean up and fix most of the problems with the office spread sheets. I tried the suggestion, I enter the both by typeing it and useing the Ctrl+;. Then went to sheet 2 and entered in the cell where I want the next date =sheet1!A1+1. My formula was automaticaly fixed to read ="[Sheet 1]1"!A1+1. Now when I entered the formula and pressed enter I got a windown UPDATE VALUES SHEET 1 and was asked to point to the work sheet I was working on. I doubled clicked the work sheet and the value I got was not todays date plus one day 12/05/2008(formated to read December 5 2008) but January 1 1900.The value on sheet 1 cell A1 is simply 12/04/2008. What I am trying to do is enter a Static Date on sheet 1 which represents day 1 of the month, sheet two is day 2 of the month and so on. I will then copy the whole sheet and paste for each day of the month so in the end there are 30 or 31 sheets in the workbook. I save the workbook with no data in it just the formtting and formulas so that when next month arrives all I have to do to change the date for each sheet is enter the date 1 time on sheet 1 and the rest of the dates for that month are automaticly inserted.

Thank you
Ken

Ok lets stay I have a row of dates and they are all different and I need to make all of them move forward by two days. Is there a way I can do this with out having to manually do it?

Ex:
12/14/08 becomes 12/16/08
12/15/08 becomes 12/17/08
12/28/08 becomes 12/30/08
Thanks!

How do I sort a column filled with dates by Month, Day, then Year? The
default seems to be to sort by year first, then month.

I have workbook with mutiple sheets, When I move or copy a sheet to a new
book, all the dates change by negative one day. What's up??

Howdy.

I'm new around here and thought it might be nice to say hi to all before I start.

I've come here in desperation to find an answer I've been seeking for days and days. I've read my beginners VBA book, googled my rear off, but no joy.

Here's my conundrum.

I have a roster sheet for work that displays start and finish times as a date and time, and formated to hh:mm. These start / finish times are scattered throughout several worksheets within 1 workbook.

Thus, the cell displays : 08:00 or 18:00 but the data in the formula bar reads as : 20/09/2005 8:00 AM or 20/09/2005 6:00 PM

What I have been trying to do is create a macro to find all these cells containing a date and time, or even just a date, and refresh the value up by 1 day. To use the example above, I want my macro to find these particular values and change them by 1 day, resulting in 21/09/2005 8:00 AM etc etc.

Being a self taught VBA beginner, this one has proven a tad difficult for me, and was hoping I could pick up the answer and some knowledge along the way from more experienced guys in here.

I'm currently at the end of a long day and must prepare for an early start tomorrow. I'll check back here in the morning.

Thanks in advance.

noddy

I'm trying to figure out how to increment/decrement date by one day in cell using a spinner.

Say for example in b2 = "5/19/10"

How would I increment/ decrement this cell with a spinner.

Thx
excelerating is online now Report Post Edit/Delete Message

I have a column that contains date/time values. If the time value for that date/time exceeds 6pm, I need to increment the date by one and set the time value to 7:00AM.

My current calculation seems cumbersome and increments the day by 1 but sets the time value to 12:00AM. If I create another column and do another calculation I can just add 7 hours but I'm assuming I can do this calculation all in 1 cell versus 2 cells.

Any help is much appreciated!

Cell value is 3/14/11 8:43 PM

Current calc is: =IF(TEXT(I585, "h:mm")*1>TIME(18, 0, 0),(DATE(YEAR(I585),MONTH(I585),DAY(I585+1))), VALUE(I585))

Returns: 3/15/11 12:00 AM

Second Calc is: =IF(TEXT(J585, "h:mm")*1=TIME(0, 0, 0),(J585+(TIME(7,0,0))), VALUE(I585))

Which returns: 3/15/11 7:00 AM

I need to create "Daily Worksheets". What I would like to do is have a front worksheet that requests "Start Date". Then I would like the next 90 sheets to increment the date slot by one day to reflect that start date + the worksheet number (i.e. start date = 2/20/2011 and the worksheet labeled "6" would have an autodate of 2/25/2011). Both the start date as well as each of the daily dates appear on cell B3. I saw an OFFSET command but could not figure out how to pull the worksheet label to add to it.

I have a schedule for work I use with excel and I have a date at the top.

a1= DUTY ROSTER
A2= THURSDAY, AUGUST 7 2008 - WEDNESDAY, AUGUST 13 2008
B3= THU AUGUST 7
D3= FRI AUGUST 8
E3= SAT AUGUST 9

Etc.. Until It Reaches H3 For Wednesday

The Problem Is That I Cannot Figure A Way Of Makinga Macro That When Ran Adds 7 Day To All Of Those Dates And Adjusts For Month Changes Such As Feb With
Shorter Days In The Month. All I Can Do Is Make The Macro Do A Specific Thing Like Always Revert To The Same Programmed Date Instead Of Adjusting It To The Next
Week. I Was Hoping To Make The Macro Correct The A2 Date And B3:H3 Dates.
Any Ideas?

Thanks

I am working on a Loop, I have most of it working except incrementing the cell.

I have this,

	VB:
	
PartN = Worksheets("Parts").Range("A3").Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
then the loop. what I can't figure out is I need for A3 to increment by one row until the loop is done. i.e. loop runs as A3, then A4, then A5 etc.. until the specified number of the loop is reached (which is basically the number of rows I have)

I tired something like

	VB:
	
 Variable 
CellN = "A3" 
Do Until xxx 
    PartN = Worksheets("Parts").Range(CellN).Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
then before the Loop statement at the bottom I put

	VB:
	
CellN = CellN + 1 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but I get a type mismatch error

hi
have a range of dates in a column
i can match an input date with this code
where the input date is in cell H20 (eg 04/04/2008)


	VB:
	
 ActiveSheet 
    Range("D:D").Select 
    Range("D:D").Find(What:=Range("H20"), After:=ActiveCell, LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Activate 
     
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the problem is that not all the dates in the range are consecutive and there may be the same date more than once eg
01/04/2008
03/04/2008
03/04/2008
05/04/2008

the problem is if the date in cell H20 (inputted by user) does not exist in column D, it all goes wrong.

what i would like to achieve is a way of incrementing the cell date by one day until it matches a date in the range, in this case if 02/04/2008 was entered 03/04/2008 would be picked up.

any help and explaination to an answer greatly appreciated

I cannot copy past a formatted date cell from one sheet to another - excel
automatically adjust the date by 2 days and 2 years. Any info?

How do I increment a cell content by one using the Macro Recorder? That is 1
becomes 1+1=2, becomes 1+1+1=3. Each time the macro is run it increments the
existing value by 1.
Thanks

Hi all,

i have a simple question. i would like to increase the month by one. i know that there is the "dateadd" function. however, coding error "type mismatch" occurs when i tried to run the macro.

this is what i had done:
dim s as string
s=format(date,"mmmyy")
range("b1").value = s
range("b2").value = dateadd("m",1,s)
current month : sep10
next month : oct10 (results from using macro)

i am pretty sure that the coding for this is very straightforward but i cant seem to find any solution online. any help would be greatly appreciated

Hi there,

I am looking for a code or macros that would print the current sheet then change the date in a specific cell 'B3' by one day before printing again for that day and so on and so forth...

Thanks in advance for any replies!

I have to see patients every 30 days and am using spreadsheet to keep track of it. I enter the date I see a patient in column B and in column C, I have the formula to increase the date in column B by 30 days. But I want Column C to update to the new patient seen date, when I insert new column inbetween Column B and column C for a new month, because I want to keep the date seen in Column B.

B C to B BB C

10/18/10 11/18/10 10/18/10 11/18/10 12/18/10

Thanks so much if anyone can assist me..................

Hi guys
Was hoping someone could advise a formula, UDF or VBA for cell B3 to increase date by 7 days on every new sheet?

Hi,

I have a column with a series of dates in.

A1
15/11/2007
16/12/2007
19/12/2007
20/12/2007

What's the best way to increase all these dates by a fixed value (and for them to remain in column A)?

The way I have currently done it in VBA is to create a formula in column B that adds a constant to these dates and then copy the results from column B to column A, overwriting all the values in column A.

Is there a better way of doing this?

Thanks

I need help in creating a formula to advance date by a year for my files (too
many to change manually)

Thanks Liz

Hi.
I have two questions.
1/ I'm trying to increase the date by 7 days, by either using the fill command & the copy & paste, but it wont allow it because it insisits that the merged cells need to be identical in size. I even tried doing it by a macro but the values i get are january 1900??
2/I dont know much in fact little about excel and would loved to learn more. What do i need to learn to be able to create my own kindof formuals etc. Thank you.


No luck finding an answer? You could always try Google.