Date calculation question

  • Thread starter Thread starter JoAnn
  • Start date Start date
J

JoAnn

I have a submission date (reg date field) & an expected due date (using
Workday to calculate 3 workdays from submission date, minus holidays). I
would like to change the due date so if the document is submitted after
3:00pm, an extra day is added.

How would I do this?

Thanks,
JoAnn
 
If you include the time in the submission date, one way:

A1: 27 February 2008 3:30 pm
B1: =WORKDAY(A1,3+(MOD(A1,1)>15/24), holidays)

assuming no holidays, this will return 4 March 2008


another, simpler, way:

B1: =WORKDAY(A1+8.99999/24,3)
 
Thanks! I'll try that.
--
JoAnn


JE McGimpsey said:
If you include the time in the submission date, one way:

A1: 27 February 2008 3:30 pm
B1: =WORKDAY(A1,3+(MOD(A1,1)>15/24), holidays)

assuming no holidays, this will return 4 March 2008


another, simpler, way:

B1: =WORKDAY(A1+8.99999/24,3)
Click to expand...
 
Your solution works great! But the change impacts other parts of my
spreadsheet.

My Expected Due Date field is actually 2 fields. I have the Workday field
column hidden (because I can't conditionally format it) and I display its
result in another regular date field so I can conditionally format it to
appear in red font if the submitter manually changes the Expected Due Date to
one that is earlier than the automatically generated 3-day date.

Although I'm sure there is probably a better way to do this ... for the
conditional formatting, I have the # of days calculated/displayed in another
hidden field (# Days). So if the value of the # days field is < 3, I apply
red font to the manually changed Expected Due Date. Since the change, the
Expected Due Date field is turning red when it shouldn't.

How can I fix this? I thought I could just do what I did before & create a
hidden Submission Date CALC field = the value of the Submission date field
(with a different date format) & then do the #days from that. Unfortunately,
when I do that, the time still carries over with it. Changing the date format
to m/dd/yy doesn't seem to strip the time from it.

Is there any way to perform calculations on dates that have 2 different
formats? I only need to have the time entered/displayed in the Submission
field. The expected due date & completion fields need just the date.

I will also need to determine the turnaround time (completed date -
submission date), which I am currently using NETWORKDAYS for.
 
Back
Top