Date Calculation
 

Hello,

I am trying to duplicate the following formula as a macro in vb:
B1=TEXT((MID(A1|1|4) & "/" & MID(A1|5|2) & "/" & MID(A1|7|2))-6|"YYYYMMDD")

I have following data in A:
20080101
20080102
....
20080131

I would like to then subtract 6 'days' from those values so that we have
this in B:
20071226
20071227
....
20080125

I've tried using the Format() function but it not displaying correctly.

Thank you

Date Calculation
 

With Range("A1")

Range("B1").Value = Format(DateSerial(Left(.Value, 4), Mid(.Value, 5,
2), Mid(.Value, 7, 2)) - 6, "YYYYMMDD")

End With



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"stevieb" wrote:

Hello,

I am trying to duplicate the following formula as a macro in vb:
B1=TEXT((MID(A1|1|4) & "/" & MID(A1|5|2) & "/" & MID(A1|7|2))-6|"YYYYMMDD")

I have following data in A:
20080101
20080102
...
20080131

I would like to then subtract 6 'days' from those values so that we have
this in B:
20071226
20071227
...
20080125

I've tried using the Format() function but it not displaying correctly.

Thank you

Date Calculation
 

Try

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-6

Mike

"stevieb" wrote:

Hello,

I am trying to duplicate the following formula as a macro in vb:
B1=TEXT((MID(A1|1|4) & "/" & MID(A1|5|2) & "/" & MID(A1|7|2))-6|"YYYYMMDD")

I have following data in A:
20080101
20080102
...
20080131

I would like to then subtract 6 'days' from those values so that we have
this in B:
20071226
20071227
...
20080125

I've tried using the Format() function but it not displaying correctly.

Thank you

Date Calculation
 

Thank you Sir!

I was missing DateSerial function. I am googling it as we speak.


"Wigi" wrote:

With Range("A1")

Range("B1").Value = Format(DateSerial(Left(.Value, 4), Mid(.Value, 5,
2), Mid(.Value, 7, 2)) - 6, "YYYYMMDD")

End With



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"stevieb" wrote:

Hello,

I am trying to duplicate the following formula as a macro in vb:
B1=TEXT((MID(A1|1|4) & "/" & MID(A1|5|2) & "/" & MID(A1|7|2))-6|"YYYYMMDD")

I have following data in A:
20080101
20080102
...
20080131

I would like to then subtract 6 'days' from those values so that we have
this in B:
20071226
20071227
...
20080125

I've tried using the Format() function but it not displaying correctly.

Thank you