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

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

- Question re incrementing Dates by one month
- Decreasing date by one day for at least a year
- Increase Dates By x Days Unless End Of Month
- Incrementing Dates
- Date plus one day
- Increment dates by 2 days
- Sorting dates:How do I sort a column filled with dates by Month, Day, then Year?
- When i move or copy a sheet, dates change by one day??
- VBA Code to find and update dates by 1 day.
- Increment/decrement a date with spinner
- Need to Increment Date and Set Time
- Increment Date based on Worksheet number
- Automatically Increment Dates By X Days
- Increment Cell By One Row In Variable
- Find Next Date In Range From Input
- Copy pasting dates from one sheet to another
- Using macro recorder increment cell by one?
- Coding for incrementing date by 1 month
- Macros or VB Code for printing a sheet and then automatically changing the date by 1d
- Automatically increase date by 30 days when column inserted
- Incremental date increase from previous sheet
- Increase Column Of Dates By X Days
- How to creat a formula to advance date by one year
- Increase date by 7

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

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'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!

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) LoopCheers

Charlie

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

Ex:

12/14/08 becomes 12/16/08

12/15/08 becomes 12/17/08

12/28/08 becomes 12/30/08

Thanks!

default seems to be to sort by year first, then month.

book, all the dates change by negative one day. What's up??

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

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

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

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 have this,

VB: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)PartN = Worksheets("Parts").Range("A3").ValueIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I tired something like

VB:then before the Loop statement at the bottom I putVariable CellN = "A3" Do Until xxx PartN = Worksheets("Parts").Range(CellN).ValueIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:but I get a type mismatch errorCellN = CellN + 1If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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:the problem is that not all the dates in the range are consecutive and there may be the same date more than once egActiveSheet 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 WithIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

automatically adjust the date by 2 days and 2 years. Any info?

becomes 1+1=2, becomes 1+1+1=3. Each time the macro is run it increments the

existing value by 1.

Thanks

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

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!

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..................

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

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

many to change manually)

Thanks Liz

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.