# Calculate date and format without showing both number and date

Closed
rlw5009 Posts 2 Registration date Wednesday January 29, 2014 Status Member Last seen January 29, 2014 - Jan 29, 2014 at 10:28 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Jan 30, 2014 at 08:40 PM
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
Related:

## 3 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jan 29, 2014 at 11:20 AM
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.
rlw5009 Posts 2 Registration date Wednesday January 29, 2014 Status Member Last seen January 29, 2014
Jan 29, 2014 at 01:41 PM
Hmm. Excel is saying the formula contains an error.. I am using Excel 2010
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jan 30, 2014 at 08:40 PM
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.