Format Date

[tvman5683]

I'm using a line of code to identify a file dated yesterday. This morning I noticed a result I didn't expect.
The result I saw was a date of "20160200". I've used the minus 1 before and never saw that. Can anyone give me any ideas??

Thanks
John

Format(Date, "yyyymmdd") - 1

 

[Joe4]

The problem is that the FORMAT function does not return a Date value, but rather a Text (string) value. So you cannot do date math like that on it like that. Subtracting 1 is coerceing it from Text to Number, but it is viewing at as the integer 20160201 (not a date, because dates aren't stored like that).

If you want to due date math like that, do it BEFORE applying the FORMAT functions, i.e.

Format(Date - 1, "yyyymmdd")

 

[Micron]

You are trying to subtract 1 from the formatted result (within the format function) when you should be subtracting it from Date:
Format(Date-1, yyyymmdd)

 

[Joe4]

Format(Date-1, yyyymmdd)

Don't forget the double quotes around "yyyymmdd" like I showed in my response above!

 

[Micron]

Crap! Not only did I post while you were posting, I forgot the quotes.

 

[Joe4]

Not only did I post while you were posting

You mean I found some one who types even slower than I do?!?!?
Usually I am on the other end (that half-semester of typing in Junior High really didn't help me that much!)

 

[tvman5683]

Thanks All! Looks like my amateur status is still in tack.