| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| End Date Calculation (adding a start date duration) | Excel Discussion (Misc queries) | |||
| date calculation | Excel Worksheet Functions | |||
| Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
| Date Calculation | Excel Discussion (Misc queries) | |||
| Date Calculation | Excel Worksheet Functions | |||
Linear Mode
