Excel String to date object? - go back a custom number of days? :) from Mr Excel


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

String to date object? - go back a custom number of days? :)
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

apwdweb
Board Regular


Joined: 24 Aug 2002
Posts: 56


Status: Online

 Reply with quote  

String to date object? - go back a custom number of days? :)

Hi All,

I'm stumped and wonder if anyone can help me figure this one out

I have a custom date stored via three integer variables a,b,c

a = 10 '10 Dec
b = 12 '12th
c = 2003 '2003 year

Suppose i need to convert this date to a date that is exactly 30 days back or 90 days or better yet a Custom number of days back.

I was wondering if there is a good way to do this accurately to reflect leap years and the number of days for each month.

Would appreciate your input.
Thanks in advance
Jennifer

Post Wed Jan 14, 2004 6:08 am 
 View user's profile Send private message

SIXTH SENSE
Board Master


Joined: 29 Oct 2003
Posts: 955

Flag: Blank

Status: Offline

 Reply with quote  

hi!
is this what you mean?

Microsoft Excel - Book1___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
E
F
1
daymonthyeardatedays backthe date 90 days back before 12- 10- 2003
2
10122003December 10, 200390September 11, 2003
Sheet2 

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

_________________
There is always a better way!!

Post Wed Jan 14, 2004 6:26 am 
 View user's profile Send private message

Chitosunday
Board Master


Joined: 14 Jul 2003
Posts: 443

Flag: Philippines

Status: Offline

 Reply with quote  

Using VBA or macro for date less 30 days

a = 10
b = 12
c = 2003
MsgBox DateSerial(c, b, a) - 30

using formula
=date(c,b,a)-30
_________________
Mr Young at Heart - Learning is like chasing the wind, it's boundless.

Post Wed Jan 14, 2004 6:35 am 
 View user's profile Send private message Send e-mail MSN Messenger

white6174
Board Master


Joined: 07 May 2002
Posts: 133
Location: CALIFORNIA

Status: Offline

 Reply with quote  

Re: String to date object? - go back a custom number of days

heres something to try
just an example I made up

hope it helps
steve w


Private Sub CommandButton1_Click()
Dim a As Variant
Dim b As Variant
Dim c As Variant


a = 10 '10 Dec
b = 12 '12th
c = 2003 '2003 year

TextBox1.Value = DateSerial(c, b, a)

TextBox2.Value = DateSerial(c, b, a) - TextBox3.Value 'textbox3 has value you want to subtract

End Sub

Post Wed Jan 14, 2004 6:39 am 
 View user's profile Send private message

apwdweb
Board Regular


Joined: 24 Aug 2002
Posts: 56


Status: Online

 Reply with quote  

Re: String to date object? - go back a custom number of days

Great! thank you!

now what if i want to convert the date back to a string after i subtract (go back) 90 days?

Thanks

Post Wed Jan 14, 2004 1:20 pm 
 View user's profile Send private message

Chitosunday
Board Master


Joined: 14 Jul 2003
Posts: 443

Flag: Philippines

Status: Offline

 Reply with quote  

a = 10
b = 12
c = 2003
ActiveCell = "'" & DateSerial(c, b, a) - 90
a = Day(DateSerial(c, b, a) - 90)
b = Month(DateSerial(c, b, a) - 90)
c = Year(DateSerial(c, b, a) - 90)
_________________
Mr Young at Heart - Learning is like chasing the wind, it's boundless.

Post Wed Jan 14, 2004 2:15 pm 
 View user's profile Send private message Send e-mail MSN Messenger

apwdweb
Board Regular


Joined: 24 Aug 2002
Posts: 56


Status: Online

 Reply with quote  

Re: String to date object? - go back a custom number of days

chitosunday

Thanks for your help. DO you know if it is possible to count 90 days backwards excluding weekends?

Thanks.

Post Sun Jan 18, 2004 5:03 am 
 View user's profile Send private message

Chitosunday
Board Master


Joined: 14 Jul 2003
Posts: 443

Flag: Philippines

Status: Offline

 Reply with quote  

'use the workday function
a = 15
b = 1
c = 2004
ActiveCell.Formula = "=workday(" & """" & DateSerial(c, b, a) & """" & ",-90)"
ActiveCell.Value = "'" & ActiveCell.Value
a = Day(ActiveCell)
b = Month(ActiveCell)
c = Year(ActiveCell)
_________________
Mr Young at Heart - Learning is like chasing the wind, it's boundless.

Post Mon Jan 19, 2004 3:14 am 
 View user's profile Send private message Send e-mail MSN Messenger

Chitosunday
Board Master


Joined: 14 Jul 2003
Posts: 443

Flag: Philippines

Status: Offline

 Reply with quote  

or use this

a = 15
b = 1
c = 2004
ActiveCell = Format(Application.ExecuteExcel4Macro("workday(""" & DateSerial(c, b, a) & """,-90)"), "mm-dd-yy")
_________________
Mr Young at Heart - Learning is like chasing the wind, it's boundless.

Post Mon Jan 19, 2004 3:31 am 
 View user's profile Send private message Send e-mail MSN Messenger
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.