Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Date calculation question
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Date calculation question
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) In article , JoAnn wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Date calculation question
Thanks! I'll try that.
-- JoAnn "JE McGimpsey" wrote: 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) In article , JoAnn wrote: 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Date calculation question - works but there's another problem
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. -- JoAnn "JE McGimpsey" wrote: 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) In article , JoAnn wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date calculation across workbooks / Workbook naming question. | Excel Discussion (Misc queries) | |||
End Date Calculation (adding a start date duration) | Excel Discussion (Misc queries) | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
A simple date calculation question. | Excel Discussion (Misc queries) | |||
A simple date calculation question. | Excel Discussion (Misc queries) |