Calculate date and format without showing both number and date [Closed]

Report
Posts
2
Registration date
Wednesday January 29, 2014
Status
Member
Last seen
January 29, 2014
-
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
-
Hello,
I am trying to enter a line item in an invoice that states the Week ending date like this "HOURS WE" then states the date on the invoice (F2 which is 1/26/14) minus 7 days. So far, I have gotten the formula to calculate formula but it includes the date as a number AND as date and does not subtract the seven days. (see below)

HOURS WE 41658 01/26/14

How do I remove the number portion and subtract 7 days so it says "
HOURS WE 01/19/14" if my formula is


=" HOURS WE "&IF($F$2="","",$F$2-7)&TEXT(F2," MM/DD/YY")

Regards

3 replies

Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
136
rlw5009, Good afternoon.

Try this one!
=" HOURS WE "&IF($F$2="","",TEXT($F$2-7;" MM/DD/YY"))

Is this what you want?
Please, tell us if it worked for you.

I hope it helps.
Posts
2
Registration date
Wednesday January 29, 2014
Status
Member
Last seen
January 29, 2014

Hmm. Excel is saying the formula contains an error.. I am using Excel 2010
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
136
rlw5009, Good evening.

Opsss............ TYPO

Before: =" HOURS WE "&IF($F$2="","",TEXT($F$2-7 ; " MM/DD/YY"))

Now...: =" HOURS WE "&IF($F$2="","",TEXT($F$2-7 , " MM/DD/YY"))

Change semi-colon to comma at TEXT function.

I hope it helps.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!