Solved

Date formula

Posted on 2013-01-04
5
284 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
Comment
Question by:route217
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
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

by:route217
ID: 38743280
Thanks Michael once again

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

Author Comment

by:route217
ID: 38743287
Solved it....
0
 
LVL 23

Expert Comment

by:Michael74
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 33

Assisted Solution

by:Rob Henson
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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

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

Join & Ask a Question