| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a date range
Hi All,
I am trying to store todays date minus x amount of days. So I get todays date with Date Function, format it to YYMMDD. Now my problem is, today is the 12, so if I minus 2 weeks I get 80999, not 080929. So it drops my leading zero and ignores the month How do I maintain a date structure across month changes. Thanks Chad |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a date range
Do your arithmetic against the real date--then format the result:
Dim myDate as date mydate = date - 14 msgbox format(mydate, "yymmdd") Chad wrote: Hi All, I am trying to store todays date minus x amount of days. So I get todays date with Date Function, format it to YYMMDD. Now my problem is, today is the 12, so if I minus 2 weeks I get 80999, not 080929. So it drops my leading zero and ignores the month How do I maintain a date structure across month changes. Thanks Chad -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a date range
Thanks Dave, always fast on the replys.
startdate = "0" & enddate - 7 'startdate equals "080999" x = Format(startdate, "YYMMDD") x = 211006 Not sure what is happening, but it is wrong. I guess it is thinking my startdate is a different format that YYMMDD Chad "Dave Peterson" wrote in message ... Do your arithmetic against the real date--then format the result: Dim myDate as date mydate = date - 14 msgbox format(mydate, "yymmdd") Chad wrote: Hi All, I am trying to store todays date minus x amount of days. So I get todays date with Date Function, format it to YYMMDD. Now my problem is, today is the 12, so if I minus 2 weeks I get 80999, not 080929. So it drops my leading zero and ignores the month How do I maintain a date structure across month changes. Thanks Chad -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a date range
It's not nice, but I broke apart the date and rearranged the numbers so that
the format liked it. It you know a better way, that would be better. Chad x = Right(enddate, 2) & "/" & Mid(enddate, 3, 2) & "/" & Left(enddate, 2) x = Format(x, "YYMMDD") "Dave Peterson" wrote in message ... Do your arithmetic against the real date--then format the result: Dim myDate as date mydate = date - 14 msgbox format(mydate, "yymmdd") Chad wrote: Hi All, I am trying to store todays date minus x amount of days. So I get todays date with Date Function, format it to YYMMDD. Now my problem is, today is the 12, so if I minus 2 weeks I get 80999, not 080929. So it drops my leading zero and ignores the month How do I maintain a date structure across month changes. Thanks Chad -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a date range
I don't understand.
I still think you'd be better off just working with the dates--not the formatted strings. Is there a reason you can't work with the real date? Chad wrote: It's not nice, but I broke apart the date and rearranged the numbers so that the format liked it. It you know a better way, that would be better. Chad x = Right(enddate, 2) & "/" & Mid(enddate, 3, 2) & "/" & Left(enddate, 2) x = Format(x, "YYMMDD") "Dave Peterson" wrote in message ... Do your arithmetic against the real date--then format the result: Dim myDate as date mydate = date - 14 msgbox format(mydate, "yymmdd") Chad wrote: Hi All, I am trying to store todays date minus x amount of days. So I get todays date with Date Function, format it to YYMMDD. Now my problem is, today is the 12, so if I minus 2 weeks I get 80999, not 080929. So it drops my leading zero and ignores the month How do I maintain a date structure across month changes. Thanks Chad -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a date range
My computer's default date style is DD/MM/YYYY, but the text in the file I
am working with has strings like 081012a (date and a letter to signify sample batch). So I don't think I can do what you say. Just because the string is a string and not a date. "Dave Peterson" wrote in message ... I don't understand. I still think you'd be better off just working with the dates--not the formatted strings. Is there a reason you can't work with the real date? Chad wrote: It's not nice, but I broke apart the date and rearranged the numbers so that the format liked it. It you know a better way, that would be better. Chad x = Right(enddate, 2) & "/" & Mid(enddate, 3, 2) & "/" & Left(enddate, 2) x = Format(x, "YYMMDD") "Dave Peterson" wrote in message ... Do your arithmetic against the real date--then format the result: Dim myDate as date mydate = date - 14 msgbox format(mydate, "yymmdd") Chad wrote: Hi All, I am trying to store todays date minus x amount of days. So I get todays date with Date Function, format it to YYMMDD. Now my problem is, today is the 12, so if I minus 2 weeks I get 80999, not 080929. So it drops my leading zero and ignores the month How do I maintain a date structure across month changes. Thanks Chad -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need a date range
You could extract the date from that string:
dim myVal as string dim myDate as date myval = "081012a" 'does 081012 represent Oct 12, 2008? mydate = dateserial(2000+left(myval,2),mid(myval,3,2),mid(m yval,5,2)) 'Then you can use: mydate = mydate - 14 'And display it anyway you want: msgbox format(mydate,"yymmdd") Chad wrote: My computer's default date style is DD/MM/YYYY, but the text in the file I am working with has strings like 081012a (date and a letter to signify sample batch). So I don't think I can do what you say. Just because the string is a string and not a date. "Dave Peterson" wrote in message ... I don't understand. I still think you'd be better off just working with the dates--not the formatted strings. Is there a reason you can't work with the real date? Chad wrote: It's not nice, but I broke apart the date and rearranged the numbers so that the format liked it. It you know a better way, that would be better. Chad x = Right(enddate, 2) & "/" & Mid(enddate, 3, 2) & "/" & Left(enddate, 2) x = Format(x, "YYMMDD") "Dave Peterson" wrote in message ... Do your arithmetic against the real date--then format the result: Dim myDate as date mydate = date - 14 msgbox format(mydate, "yymmdd") Chad wrote: Hi All, I am trying to store todays date minus x amount of days. So I get todays date with Date Function, format it to YYMMDD. Now my problem is, today is the 12, so if I minus 2 weeks I get 80999, not 080929. So it drops my leading zero and ignores the month How do I maintain a date structure across month changes. Thanks Chad -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
| copy date based on date -refer to date range | Excel Programming | |||
| Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
![Chad[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
