Free Microsoft Excel 2013 Quick Reference

automatically increment date (date= day of week, month, day, year) on successive page

Increment Date Based on Number of Pages Printed
I have a 1 page form.
I need to have the date printed and increment forward 1 day for each page I print.
I do not want to have to manually enter in the date on each page

Is there a way to make it so that if I print 30 or 31 pages it will give me a
different date for each page?

I have columns labeled MON TUE WED THU FRI SAT SUN.
In VBA, I want to check the day of week and hide any columns including or greater than that day of week.

I.E: On Monday, before 9:00am all columns show. After 9:00am show column MON only.

On Tuesday, show column MON and TUE
On Wednesday, show column MON, TUE and WED

On Sunday All columns show.

Can this be done in VBA? If so, any code snippets would be appreciated.

I am using Excel for a time sheet Schedule but i am changing Manually every month the sheet names for the coming month's dates. I just found A very good VBA code i can use in thread and it worked very good for me but i need to change it to include the day of the week and year as well, see sample file. also in column C1 i also have the date so i would like it should list the sheet name so it will advance with the date as well. Thanks In advance.


Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)

Thanks in advance

I have a column of dates say in col B. In col-A i want the following:

ColA ** ColB
1/1/11 ** 1/14/11
1/1/11 ** 1/28/11
2/1/11 ** 2/4/11
1/1/11 ** 1/12/11
3/1/11 ** 3/14/11

Basically, i have a col dates in B.....i want the first day of that month/year in col-A.

I am enclosing a file showing what i want. I can do it in excel but not sure how to "efficiently" do it in vba.

Hi -

I simply don't have the skills yet to handle this issue.

I am trying to push some original due dates to specific days of the month depending on the range in which the original due date falls.

The orignial due date gets revised during processing to one of the 3 following days of the month: the 5th, the 20th, or the 25th.

Right now it is being done MANUALLY but there are 1,000s of entries that must be processed, and i'm pretty sure it can be done automatically.

The formula should only look to the day of the date. But the raw data will display the month and year and the resulting data should display the month and year.

So if the DUE DATE is >1 but < or equal to 5, the REVISED DATE should return the 5th.
If the DUE DATE is >5 but < or equal to 20, the REVISED DATE should return the 20th.
If the DUE DATE is >20 but < or equal to 25, the REVISED DATE should return the 25th.
If the DUE DATE is >25, it should return a BLANK.

Here is an example of the data:

9/4/2011 FORMULA WOULD RETURN 9/5/2011
9/12/2011 FORMULA WOULD RETURN 9/20/2011
9/22/2011 FORMULA WOULD RETURN 9/25/2011

Let me know if i left out any info.

Thanks everyone!


Please could you give me some advice.

I have a Column of dates,( 1/9/2007, 6/8/2007 etc ) from two different sources. When i create a pivot table and use the date field I want it to group the dates in MONTHS ( Sept 2007, Aug 2007 etc ) and not DAY of the month. I tried to use the group option but it does not allow me.

How can i remove the day and only have the month ( Sept 2007 ) in my source data. So that my Pivot table will group it all by month and year. I have tried everyting in my pivot table but it puts it in as 1/4/2007 etc.


I have a list of information for a year and it is broken down into days of the week. I am looking for a calculation that can tell me what the total is per day of week.

in Column A I have the dates (DD-MMM-YY) and in Column B I have the Day of Week [=weekday(A1,2)] So Monday returns a 2, Tuesday returns 3, etc.

In the following columns I have my various data. for the sake of ease, I will me all of the data +/- 2

Monday +2
Tuesday +2
Wednesday -2
Thursday +2
Friday -2

Monday -2
Tuesday +2
Wednesday -2
Thursday -2
Friday -2

The return for these two weeks would be as follows:
Monday 0
Tuesday 4
Wednesday -4
thursday 0
Friday -4

I am looking for a formula that will automatically calculate the return.

I have tried a countifs formula but that only returns the number of cells with Mondays or Tuesday, etc. IT does not add up the values within the cells.

I appreciate your help

I have a form that users input the date (not just todays date) I want another cell to take that date and input the corresponding day of week.


user inputs 03/10/09 into cell h17 I want cell A12 to automatically have the corresponding day of week (wednessday)

Is there a way of getting the Last Day of any Month with the Year Ex: Mmm-YY?

Dear Forum,

I want to use a Date-Range in a SumProduct Formula by putting the Start-Date and the End-Date for any Month but how do i get the last date for any Month..

Though its simple, I want to keep it Dynamic so that each Month it changes and the date does not need to be changed

I need to check the payments received in a span of three months.
i.e Present Month - 2, Present Month - 1 , Present Month

So the Column Headings are made using the Date Function as below:
But in my SUMPRODUCT FORMULA... I need to check the Payments falling in the Range as below:

I need to get the number which is for the Max Range as Different Months and Feb of Different years has different End

Warm Regards

hi excel wizards.

i know that as a brand new member of a forum it is bad form to create a thread right out of the gate, but i need excel help to finish a project at work by the day's end, so internet protocol be damned.

here is what i am trying to do.

i need to know how to set up a sheet so that when i enter the weekday name and date of the month's first workday, it auto populates the rest of the month in weekday names and dates throughout the sheet.

i.e. if i enter "mon" in cell A2, i need cells A6-A36 to auto populate with the corresponding days of the week until the end of the month (excluding sat and sun)

if i enter 4/1/2009 in cell B2, i need cells B6-B36 to auto populate with the corresponding weekday dates (excluding weekends) until the end of the month as well.

i hope someone can find it in their hearts to help a fairly noobish excel user out.

1 2 3 4 5 6
11/1 11/2 11/3 11/4 11/5 11/6
sat sun mon tues wed thur

what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week

cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###. what did i do wrong


I have a pivot table with first column being dates list for 3 years in increment of 1 day.

I want to filter this column to show only the dates where the day of the month equals 30.

Ex: 1/30/2011, 3/30/2011....5/30/2011..etc.

How do I do that in a pivot table - I use excel 2010.



I have a long list of dates on one sheet, in column E, and a list of holiday dates on another sheet (the array is: 'Study Conditions'!B10:B27). In another column, next to the list of dates, I have:


I'd like to add to this, that if the date happens to be on the list of holidays, to return the value "Holiday". So basically, that cell with either contain a day of the week, or a "Holiday".

Any help is appreciated.

Hi Guys,

Hope all is well!

I got a quick question. Say I get a month (ie. 1, 5 etc) and a year from an ext database.

Is there a way that I could check what was/is the last day of that month in that particular year from the system calendar?

I just need some pointers on how to query the sys for the history dates.

Or maybe there is some other way to do this without the sys calendar.

Thanks in advance


I have a cell showing the date (01-Oct-05) with the cell formatted as “date”. Is there a formula that I can use to change the date in column A2 to a day and date in column B2.

I have tried putting the day of week in B2 and using the furmula “=A2&”-“&B2” to join the 2 cells together, but I get the day followed by numbers in place of the date? I have also tried changing the date format to custom dd-mmm-yy but still the same problem.

return last day of a month =DATE(YEAR(A1),MONTH(A1)+1,0)

but if I want to add a string to it in the same cell, how to do it?

i.e at a9
I try using = "Total Days" & =DATE(YEAR(A1),MONTH(A1)+1,0)

Hello all,

Can anyone help me with a date formula which is day of week dependent?

I need to enter the date of a credit card transaction into Excel, then calculate (in the next column) when it's due based on the following criteria:

- Any transactions created from Monday to Sunday will become due the following Friday plus four weeks.

For example,

- if I were to make a transaction today (4th June) it will be gathered next Sunday (8th June) then get credited the following Friday (13th June) plus four weeks (11th July).

- if I were to make a transaction next Sunday (8th June), it will be credited the same day as the last example (11th July).

- if I were to make a transaction next Monday (9th June), it will be gathered the following Sunday (15th June) and get credited on 18th July.

Any help would be greatly appreciated.

Many thanks chaps

I have 1/1/2008 in cell A2. I would like a formula that will give me the first day of the month for future months in the cells below. For example in cell a3 I would like 2/1/2008, cell a4 3/1/2008, etc. That way I only change the date in cell a2 and it automatically changes the cells below.


i should know this, but i seem to be stuck on this one........

i am compiling financial data on companies and i can pull the current finanical year of a company from a third party source (bloomberg) which gives me the year the month.

what i need to do is put the date in my spread sheet that also has the final day of that month.

for example, i know the year end of a company is 30 April 2008, i can pull the 04/08 down but i need to be able to pull the 30 from excel.

any ideas?

I have a simple time sheet for calculating my hours worked for a two
week pay period. In one column, I have the dates of the pay period. In
the next column, I have the day of the week.

Is there a formula I can use that will auto insert the day of the week
according to the date I insert in the date column?

D4=Date E4=???


Ltat42a's Profile:
View this thread:

What I'm trying to create is a sort of calendar that has the 7 days of the
week as the column headers. I want the headers to display the day of the week
and the date depending on what week it is. So if it's the week of September
9th, I want A1 to show 9/9/2007 - Sunday, A2 would be 9/10/2007 - Monday,
etc. Then, the following week I want the headers to change so that A1 is now
9/16/2007 - Sunday.

Of course, this is simple to do manually but I don't want to have to change
the headers every Sunday... is this even possible?


I would like to convert a date to a day of the week

ie column 1 column 2
5/7/05 Tuesday
6/7/05 Wednesday
etc etc

I know the formula for date - what is it to relate day of the week to
the date


andrewm's Profile:
View this thread:

Return Date of Last Chosen Day of Given Month Custom function that displays the date of the last chosen day in any given month

Return Day Of Week Of Excel Date
Return Day Of Week Of DateReturn Day Of Week Of DateWhen finding the weekday associated with any date, most of us would rather see it returned as a name of the day, rather than as a number (the default).Here we show how to extract the weekday of any date by using the WEEKDAY function. By default, the day is given as a whole number, ranging from 1 (Sunday) to 7 (Saturday). However, this is often meaningless, and we usually would rather see the weekday returned as a name, such as Monday/Mon, Tuesday/Tue, and so on.Get the Weekday as a NumberBefore getting to the name of the day, we'll need to begin by extracting the number of the weekday. Here's the syntax for the WEEKDAY formula:WEEKDAY(serial_number,return_type)Lets say we want to return the weekday number of 31-Jul-2007 The serial_num is any valid date in this case 31-Jul-2007 and the return_type is a number that refers to the type of return value. Depending on what result you are looking for will determine the return_type that you will use:return_typeDay of Week
1 or omittedNumbers 1 (Sunday) through to 7 (Saturday)2Numbers 1 (Monday) through 7 (Sunday)3Numbers 0 (Monday) through 6 (Sunday)We will use the default function by omitting the return-type. Click in cell A1 and type in a valid Excel date, such as 31-Jul-2007 (which is a Tuesday). Then, click in cell B1 and enter the following formula:
=WEEKDAY(A1)This formula will return the number 3, which equates to Tuesday, which is the day of the week that 31 July 2007 is.An alternative would be to hardcode the date like this:
=WEEKDAY("31 Jul 2007”)Return the Weekday as Weekday NameRemember, the WEEKDAY formula shown in the previous section only returns the weekday as a number. There are at least two ways we can use formulas to force Excel to show the actual name of the weekday.
The first method is perhaps the simplest, and all you need to do is apply a custom number format of DDD or DDDD. Again using 31-Jul-2007 as an example, select the date cell, right-click, go to Format Cells, and then choose Number tab>Custom. Enter in the custom format DDD under Type and click OK. You will get Tue in your cell.Another, probably safer way is to reference the date cell (e.g., =A1) and format this cell with a custom number format of DDD or DDDD. The big advantage to this method is that it leaves our true underlying date as a valid Excel date.
 Return the Weekday as Weekday TextIf you won't be using the weekday that is returned in any further calculations, you can use either of the three formulas that follow (TEXT, WEEKDAY with CHOOSE) to return the weekday of a date as text.This formula assumes you have a valid date of 31-Jul-2007 n cell A1:
=TEXT(A1,"DDDD") will produce Tuesday, or you could hard code your formula like:
=TEXT("31 Jul 2007","DDDD")
you could use the slightly longer CHOOSE function to get the same result:
=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")and finally, you can hard code the date in the CHOOSE and nest the WEEKDAY function within it like:=CHOOSE(WEEKDAY("31 July 2007"),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") ")All of these formulas will return the same result - Tuesday, remember though the underlying value of your cell will still be 31-Jul-2007, even though your cell reads Tuesday.