Date formula based on =now()?

billyvan

New Member
Joined
May 22, 2007
Messages
29
I am trying to create a formula that converts the =NOW() function into a month format for the previous month. for example I want a formula that converts a cell on a spreadsheet to August-07. Can anyone please help?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
How do you want days handled? If Now() is Sep 4, should your desired date be Aug 4? Or would any day in August be okay (Aug 1, for instance).

Regards.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I am trying to create a formula that converts the =NOW() function into a month format for the previous month. for example I want a formula that converts a cell on a spreadsheet to August-07. Can anyone please help?

=EDATE(TODAY(),-1)

Custom format the formula cell (Format|Cells) as:

mmmm-yy
 

tonyibm

New Member
Joined
Sep 3, 2007
Messages
4
=TEXT(DATE(YEAR(NOW()),MONTH(NOW())-1,DAY(NOW())),"mmmm-yy")

this formula returns the same day of the previous month,
 

billyvan

New Member
Joined
May 22, 2007
Messages
29

ADVERTISEMENT

thanks tonyibm

I have used the formula that you suggested and it works perfectly.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
=TEXT(DATE(YEAR(NOW()),MONTH(NOW())-1,DAY(NOW())),"mmmm-yy")

This formula won't always give the previous month, e.g. on 31st October 2007 it'll return "October-07". If you always want the previous month try

=TEXT(TODAY()-DAY(TODAY()),"mmmm-yy")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top