Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date formula

Posted on 2013-01-04
5
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 1000 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:Michael Fowler
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 1000 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

715 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