Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Formula to calculate the number of days till end of month

1. Member
Join Date
30th January 2005
Location
South Africa
Posts
21
Usergroup
Registered Users

## Formula to calculate the number of days till end of month

Hi

I’m looking for a formula that will calculate the number of days left in a month when the formula looks at any day in the month.

For example – if A1 = 2006/05/14 then the formula in A2 that look at A1 must give the answer 17

I tried every thing I know of but nothing wants to work.

Thanks for you help

Neels

Excel Video Tutorials / Excel Dashboards Reports

2. Established Member
Join Date
2nd March 2006
Location
London
Posts
988
Usergroup
Registered Users

## Re: Formula to calculate the number of days till end of month

I can think of 2 ways straight away. The first one requires that you have the Analysis Toolpak added in, and is:
VB:
```=EOMONTH(A1,0)-A1

```
and the other one is:
VB:
```=(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-A1

```

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Formula to calculate the number of days till end of month

Another way without ATP

=DATE(YEAR(A1),MONTH(A1)+1,0)-A1

you need to format the cell as General though

4. Established Member
Join Date
2nd March 2006
Location
London
Posts
988
Usergroup
Registered Users

## Re: Formula to calculate the number of days till end of month

Yes, Bob, my second one works that way too. I said that the first one needs the Analysis Toolpak, not my second one.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Formula to calculate the number of days till end of month

Originally Posted by GlennUK
Yes, Bob, my second one works that way too. I said that the first one needs the Analysis Toolpak, not my second one.
Sorry I mis-read the VBA tag as suggesting in VBA (doh!). But at least I do show how to use aday number of 0, avoiding the extra subtraction <G>

6. Established Member
Join Date
24th March 2005
Posts
507
Usergroup
Registered Users

## Re: Formula to calculate the number of days till end of month

I think using the DATEDIF and NOW function will be best in this situation.

=DATEDIF(A1,NOW(),"d")

Excel Video Tutorials / Excel Dashboards Reports

7. Established Member
Join Date
2nd March 2006
Location
London
Posts
988
Usergroup
Registered Users

## Re: Formula to calculate the number of days till end of month

Fin Fang Foom,

please explain how that would give the number of days until the end of the month?!?!?

Excel Video Tutorials / Excel Dashboards Reports

8. Established Member
Join Date
24th March 2005
Posts
507
Usergroup
Registered Users

## Re: Formula to calculate the number of days till end of month

Hi GlennUK,

Aw I should've read the post more carefully. Your formulas would be efficient.

Excel Video Tutorials / Excel Dashboards Reports

9. Member
Join Date
30th January 2005
Location
South Africa
Posts
21
Usergroup
Registered Users

## Re: Formula to calculate the number of days till end of month

Thanks a million GlennUK - It works like a dream - "I can imaging a world without writers like you" - This forum is unbelievable.

Excel Video Tutorials / Excel Dashboards Reports

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

#### Posting Permissions

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