Solved

# Date formula

Posted on 2013-01-04
259 Views
Last Modified: 2013-01-07
Hi Experts

I have in cell c2 ="w/e"&text(now(),"dd mmm yy" which is fine but I want to insert a similar
Formula in cell b2 less 7 days and in a2 less 14 days...
0
Question by:route217
• 2
• 2
5 Comments

LVL 23

Accepted Solution

Michael74 earned 250 total points
ID: 38743264
="w/e "&TEXT(NOW() - 7,"dd mmm yy" )

and

="w/e "&TEXT(NOW() - 14,"dd mmm yy" )

Michael
0

Author Comment

ID: 38743280
Thanks Michael once again

How would I amend to show date as Friday 04 Jan in cell c2
0

Author Comment

ID: 38743287
Solved it....
0

LVL 23

Expert Comment

ID: 38743288
As text use

="TEXT(NOW(),"dddd dd mmm" )

as a date just use =Now() and use custom format the cell to "dddd dd mmm"

Michael
0

LVL 31

Assisted Solution

Rob Henson earned 250 total points
ID: 38743518
Rather than using TEXT function to add the "W/E" use a custom format of:

"W/E " dddd dd mmm

The dates can then be just:

=NOW() or =TODAY()

Then you can use =NOW()-7 and =NOW()-14

Or to round a date down to previous week end:

=FLOOR(NOW(),7)

Thanks
Rob H
0

## Join & Write a Comment Already a member? Login.

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

#### 707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!