complex formula help!

[Savio]

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

 

[Pete_UK]

Show us the formulae you have at the moment, and give us some detail
about how your data is laid out and what you want to do with it.

Pete

 

[Bob Phillips]

Try

=IF(MOD(A2,1)>=TIME(17,0,0),IF(WEEKDAY(A2)=6,INT(A2)+3,INT(A2)+1),INT(A2))

 

[Savio]

this is the current formula

=-(IF(AND(WEEKDAY(A1,2)<6,MOD(A1,1)>17/24),1,0)+WEEKDAY(A1,2)>6)+2*(IF
(AND( WEEKDAY(A1,2)<6,MOD(A1,1)>17/24),1,0)+WEEKDAY(A1,2)>5)+A1

is there any way of putting this into VBA to use with a user form?
one column in the spreadsheet is used to log the date and time when
the query is logged. i can use two cells if necessary. i want the
other cells in the second column to log the date according to the
following conditions depending on the date entered.
if the query is logged mon-fri before 5pm then the cell should contain
the same date as the day the query was logged
if the query is logged sat/sun at any time then the next working day
ie monday's date should b used
if the query is logged after 5pm mon- thurs then the next working
day's date is used
if the query is logged after 5pm on friday then the next working day
ie monday's date is used.

thanks

 

[Ron Rosenfeld]

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

If you are using Excel 2007+ or if you have the Analysis toolpak installed for
earlier versions, you could use this:

=WORKDAY(A1+TIME(6,59,59)-1,1)

If you get the NAME error, look at HELP for the Workday function for corrective
measures.
--ron