# Return Month Number Of Date Unless Cell Empty

Apr 9, 2008I am using the MONTH function in a formula. The Month value of an empty cell is returned as "1". Why is this? Is there any way to return a null value or zero?

View 6 RepliesI am using the MONTH function in a formula. The Month value of an empty cell is returned as "1". Why is this? Is there any way to return a null value or zero?

View 6 RepliesADVERTISEMENT

I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09

1 Jan 09

1 Jan 09

1 Jan 09

2 Jan 09

2 Jan 09

3 Jan 09

3 Jan 09

3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

If I make a sale between the 1st of each month through the 1th of each month I will be paid on the 31st of that month. If I make a sale on the 16th of each month through to the last day of each month I will be paid on the 15th of the next month. I need this formula to work for every month.

cell e has date of sale

cell h has the returned value of the formula above

I need to return the date of the third day of the month following the date in cell a1:

So, If A1= 06/01/12

A2 should = 07/03/12

Is there a end of "week" function which shows only the date the friday? says ..if today is 9/13/2007, the cell will show 9/7/2007...

View 3 Replies View RelatedI'm after a formula(s) which will give the start date and end date of a month using the month name. For instance, if cell A1 contained OCTOBER 07 the formula result for the start of the month would be 01/10/07 and the formula for the end of the month would be 31/10/07. I have plyed around with the EOMONTH function but this only workd when an actual date is entered.

View 8 Replies View RelatedUpon trying to use the Month function (to return the month integer from a date), in excell 2007, I get the

"Expected variable or procedure, not module

followed by (if I drill down into the help file)

There is no variable or procedure by this name in the current scope, but there is a module by this name"

error message

Do I assume there is a way around this (or if I have to do something to 'activate' the month function)

I have some data & want to count the individual number of days for each month.

Example attached..

I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.

Is there any way to defined our own fiscal month. I have a fiscal year where the start & end date is different from the normal calendar. I have store the start & end date on the different column. What i need is that when people enter a date, it will look up to the table and return the fiscal month.It's something like If the value is >=column A and <=column B, then the fiscal month=column C(refer to the below table). But i have no idea how to make comparison on date value. what kind of formula i should use? And also how to write the code if i want to use macro to implement this?

Start End Month

09/21/08 10/18/08 Oct

10/19/08 11/22/08 Nov

11/23/08 12/27/08 Dec

12/28/08 01/24/09 Jan

01/25/09 02/21/09 Feb

02/22/09 03/28/09 Mar

03/29/09 04/25/09 Apr

04/26/09 05/23/09 May

05/24/09 06/27/09 Jun

06/28/09 07/25/09 Jul

07/26/09 08/22/09 Aug

08/23/09 09/26/09 Sep

formula off here i use all the time relating to finding and sumproducts for specific months and years i.e.

Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula...

is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009?

The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.

I have a cell (A1) which contains the month. A2 contains the year. I need a formula which will return each date of the month.

View 7 Replies View RelatedHow to write a VBA code so that it would calculate the date of the previous Friday and from there Return the date exactly 1 month before; on any given day.

For example...

today is may 8th

So if I run the code the dates returned for last friday should be 05/02/2014

and 04/04/2014 should be returned AS the DATE exactly a month from 05/02/2014

I would like to use the IF function so when cell B2 is empty, cell C2 will stay empty. the formula =MONTH(B2&C2) need to be used when cell B2 is not empty. this to convert text to number in column C

This is the result that I would like to achieve

A

B

C

D

E

1

MONTH IN TEXT

MONTH IN NUMBER

2

MAY

5

3

4

JUNE

6

I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.

I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.

VB:

Dim Cell As Range

' 1st cell with the posting date

Set Cell = Range("A2")

Do While Not IsEmpty(Cell)

If Cell = "Date" Then

[Code] .....

In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)

The Week ( Monday to sunday) which need to be calculated is the week no. in the given month

like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april

how to convert date variable to month in running number. the starting date = 1996 Jan =1 and 1996 Feb =2 .... and so on.

as i plan to generate p table report for view more then 12 month data.....

I want to get a return value of date in column C whereas column A represent a date and column B represent a number. Simplest formula in excel is Column C (Feb 26, 2014)= Column A (Feb 21, 2014) + Column B (5). I want it to have in macro code.

View 14 Replies View RelatedI have this formula =INDIRECT(ADDRESS($GS1,COLUMN()))

and when a cell is empty this formula return a 0 but I would like it to return also an empty cell because in my data the 0 count for a value.

Then I have this one : =COUNTIF($ML641:$NA641,$ML653)

I would like to do the same thing to not return a 0 if the cell is empty but an empty cell. I hope I explain it right ?

I would like to calculate the week number of the month based on a date.

Now my days would only include working weeks (Monday - Friday).

Supposed the date is 12/31/2012:

M

31-Dec

T

1-Jan

W

2-Jan

TH

3-Jan

F

4-Jan

Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:

M

28-Jan

T

29-Jan

W

30-Jan

TH

31-Jan

F

1-Feb

It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:

M

29-Apr

T

30-Apr

W

1-May

TH

2-May

F

3-May

It will be considered as Week 1 of May since it occupies only 2 days of the working week.

Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.

I have a a set of date ranges. The start and stop date of the ranges are listed in seperate columns. I need to count how many of those date ranges include a specific month/year. Example data is below.

Start

Stop

Month/Year

[Code].....

I have a problem with finding the right function. My goal is to have the following function...

The cell D1 has the following function: [=IF(B1<=C1;"YES";"NO")] But I would like to have the cell D1 to be left blank, if the cell A1 is blank.

find out the last blanck cell in the row. ex: 1 2 0 5 6 0 0 5 0 zero it means blanck i prefer using fx function but i can use also Macro

View 6 Replies View Relatedi am trying to create a forumla in a column (D in my expamle below) to count the number of days between two dates. Knowing that col D will change everytime the @now date changes - but thats ok..

COL A COL B COL D

ROW 1 Open Date Closed Date Count of days open

ROW 2 4/1/07 BLANK CELL Formula result here

ROW 3 4/1/07 9/5/07 Formula result here

if col b has a date then subrtract col a from b and display the # result, if col b is blank then subtract col a from location I store the @ now date - say Col ZZ Row 99? so no matter there is a count of number of days in every row in column D

I want formula which can automatically populate the amount based on the number of months starting from the date vehicle is received. For Eg.

Nissan patrol Rent is AED 5800/- per month & we deliver it on month Sept 2011 for 24 months then i want excel to automatically populate AED 5800/- for next 24 months starting from Sept 2011.

I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. Any way to identify the number of days, per calender month, that falls in a date range.

sample data...

Start Date

End Date

Old Value

New Value

08/03/2010

18/06/2010

16758.2

16758.1

[Code] .......

I need to break down the total number of days per month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

24

30

31

18

[Code] ......

As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...

I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.

(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it).

I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.

It is a monthly reset so it has to roll on from the previous month.

Here is what i have currently but it isn't working.

Code:

' Get user to input the first day of the new month to populate all dates with

dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _

Title:="Enter Date")

' Validates the entered date is a valid date

If (IsDate(dNewMonth) = False) Then

[Code]...

I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10

10/15/2009 01/13/2009

I'm using Excel 2007.

column a has dates & column d has values - what is formula to find highest number in values and return it's date (using =large(Dxx:Dxxx, 1) to find highest number

View 2 Replies View RelatedHow can I use an if loop to return the first number of a date.

For example, if cell A1 has the date: 7/06/2006, I want to say:

If (ActiveCell.Characters(Start:=1, Length:=1).Text = "7") Then

TodayInt = 7

End If

But this is trying to look for a string and its finding a date, so I

get an error...

Copyrights 2005-15 www.BigResource.com, All rights reserved