Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Date formula

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
route217
Asked:
route217
  • 2
  • 2
2 Solutions
 
Michael FowlerSolutions ConsultantCommented:
="w/e "&TEXT(NOW() - 7,"dd mmm yy" )

and

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

Michael
0
 
route217Author Commented:
Thanks Michael once again

How would I amend to show date as Friday 04 Jan in cell c2
0
 
route217Author Commented:
Solved it....
0
 
Michael FowlerSolutions ConsultantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now