Solved

Date formula

Posted on 2013-01-04
5
266 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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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 will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now