Here's the question. do you want it to be accomplished by the userform or a change event on the sheet?
I created this change event: Private Sub Worksheet_Change(ByVal Target As Range) Dim CurDateTime, CurTime, CurDate, CurDay If (Target.Column > 1) Then CurDateTime = Now CurTime = TimeValue(CurDateTime) CurDate = DateValue(CurDateTime) CurDay = Weekday(CurDate) If CurTime > "17:00" Then CurDate = CurDate + 1 End If If CurDay = vbSaturday Then CurDate = CurDate + 2 ElseIf CurDay = vbSunday Then CurDate = CurDate + 1 End If End If Application.EnableEvents = False Cells(Target.Row, 1) = CurDate Application.EnableEvents = True End Sub I used different time-of-day and day of week values to test it. but it seems to work. Paul ----- Original Message ---- > From: Savio <saviob...@hotmail.com> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > Sent: Monday, February 2, 2009 8:57:42 AM > Subject: $$Excel-Macros$$ complex formula help! > > > I'm designing a spreadsheet to log specific details based on the > present date and time. Basically if an entry is logged on a day from > Mon-Fri before 5pm, then a cell value will have the present date. If > it is logged anytime on sat/sun the cell will have the next working > day (Monday) date. If an entry is logged after 5pm, Mon-Thurs, then > the next working day date is entered into the cell. For friday this > would mean Monday's date. > I have the individual formulas for each of these situations. However > i > need to integrate the formulas into a user form so that all this is > done at the click of a button. Is there a simpler way to do this in > VBA or would i need to combine all the formulas into one and keep > pasting them into every cell that requires the data? > Thanks > > --~--~---------~--~----~------------~-------~--~----~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com If you find any spam message in the group, please send an email to Ayush @ jainayus...@gmail.com -~----------~----~----~----~------~----~------~--~---
