calculate date and format without showing both number and dateClosed

Question

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

Mazzaropi · Answer

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 · Answer

Hmm. Excel is saying the formula contains an error.. I am using Excel 2010

Mazzaropi · Answer

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.

Calculate date and format without showing both number and date

3 responses

Similar discussions

Subscribe To Our Newsletter!