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
0
JoAnn (49)
2/27/2008 1:49:09 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
689 Views

Similar Articles

[PageSpeed] 11

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 <C26E5809-869A-4732-A977-CDC0C7BACB44@microsoft.com>,
 JoAnn <JoAnn@discussions.microsoft.com> 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
0
jemcgimpsey (6723)
2/27/2008 2:01:08 PM
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 <C26E5809-869A-4732-A977-CDC0C7BACB44@microsoft.com>,
>  JoAnn <JoAnn@discussions.microsoft.com> 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
> 
0
JoAnn (49)
2/27/2008 2:59:01 PM
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 <C26E5809-869A-4732-A977-CDC0C7BACB44@microsoft.com>,
>  JoAnn <JoAnn@discussions.microsoft.com> 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
> 
0
JoAnn (49)
2/27/2008 5:52:00 PM
Reply:

Similar Artilces:

Most unusual question
Using Outlook Office 2003 and this evening, everytime I try to open a link from email in Outlook, it opens up My Documents! What's wrong and how do I correct this problem! Thanks! ...

Outlook 2002 Question #2
Hi, I have configured Outlook to get email from 2 different accounts: a regular POP3 account and a Hotmail account. When I have Outlook open and a new message arrives in the POP3 account, I get a notification box and I hear a sound. However, when a new email arrives in my Hotmail Inbox, I neither get a notification nor do I hear a sound. So, how do I configure outlook to pop a notification window and play a sound when new mail arrives in my Hotmail Inbox? Thanks, Mr. Camel You can't. Login with your Passport with MSN Messenger to be notified when a new message arrives in yo...

question about data...
I am trying to use data on one worksheet as part of another. However, when I copy & paste the data, it copies the formulas instead of the numbers. Using references to the other worksheet doesn't work because on the other worksheet I need to sort the data differently. Is there any way to change the results of a formula to just plain "numbers" in excel? -- caligula11 ------------------------------------------------------------------------ caligula11's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35526 View this thread: http://www.excelforum.c...

Questions re Activities
Is there a way to add the Date Created column to the Activity display? At present I may have a Case or a Contact with lots of activities but they don't appear in chronological order and I have to open each one up and go to File/Properties menu to find the date - would be much easier to do a sort by date. And..does anyone know what the Activity Category and Sub-Category fields are for? There is nothing in the online help and these fields are not listed via the Advanced Find area and so are not searchable. Thanks in advance. Sue ...

Two Question ---
Luck, I have two problems First: I want to change the text of an edit (of course, belong to CView class) from the CWinApp class Second: I'm trying to detect when the mouse pointer is over a picture control and I also need to detect when the mouse click on the picture control. I put its Notify property to true, but this let's me detect the click event, but give me some troubles to detect when the mouse is over Please, somebody help. I'm block with that. Thanks in advantage. > > First: I want to change the text of an edit (of course, belong to CView cla...

date formulas
Hi, I have two problems that need help. Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a dob of birth, cell A2 returns 110. Another one is having cell C1 with a date or NA. C2 is based on cell C1 and adds 3 months to the date in C1 but if C1 has NA, the return is #value!. Kathleen Describing the problem is only the first step, Kathleen. You also need to identify the solution you are looking for. As a guess, for #1 try, =if(a1="","",datedif(a1,today(),"y")) For #2, =if(c1="NA","",date(y...

Question regarding Trial Version
I am not able to customize Forms (like Leads etc) using the trial version. Does anyone know whether i can login to MS CRM as Administrator to customize the forms? It currently says no privilege while clicking on Customize. the trial version is a fully-functioning version. you need to be granted the System Administrator role or, at minimum, the System Customizer role. by default, the user that installed the CRM will have the System Administrator role and will have the rights to grant that or any role to you. Dave "Ayyappa Das" <AyyappaDas@discussions.microsoft.com> wr...

COUNTIF question
Hey all, i'm stuck. I need to do a formula that will count the number of cells in column 'A' only if the corresponding cell in column 'B' has a value of '0'. =COUNTIF(O:0,"0")+COUNTIF(P:P,">0") - just counts both, and that's as far as i have got. Can anyone help me please. Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ This is probably a cheats way of doing it but... I would create a third column with...

HELP!! Accounting Question
When I run a Tender Summary for specific days, I don't see a record of the Payments to Account that have been made for that specific day. Why? My accountant desperately needs to know why. Also, why when I run a Detailed Sales report and a Tender Summary for the same period, do the amount not match up. Thank you in advance for your help. See my new thread question from today for a better Z report that can be run from manager. I think we are talking about the same thing. Maybe someone out there can help. "knightsbridge" <knightsbridge@discussions.microsoft.com> w...

Protecting a worksheet question
I have a question for Excel 98. I tried to do a simple worksheet protection and provided a password. However, I found that not ALL cell in that particular worksheet was protected. Most of the cell would give you a message telling you the worksheet is protected and you need to unprotect the worksheet to modify the cell content. However, some cells you can still change its content. What are the reasons these things happen? Thank you! Each cell has a locked property that you can toggle. But the "lockedness" doesn't really do anything until the worksheet is protected. Then l...

Balance of OT calculation
Hi Please help to set the formula for the calculation of "Balance of". Thanks 3 a b c d e f g h I 4 Apr 04 May 04 Jun 04 Jul 04 Aug 04 Sep 04 Oct 04 Nov 04 Dec 0 5Overtime: 1st 3 hrs 10.0 0.0 10.0 0.0 10.0 10.0 10.0 10.0 10.0 6 4th hrs 0.0 5.0 1.0 0.0 2.0 2.0 2.0 2.0 2.0 7Total claim 10.0 5.0 11.0 0.0 12.0 12.0 12.0 12.0 12.0 8Total set-off 3.0 11.0 1.0 17.0 1.0 1.0 1.0 1.0 1.0 9 10 B/F 7.0 1.0 11.0 (6.0) 5.0 16.0 27.0 38.0 49.0 11 12 Balance of: 13 April 7....

Another NameTable Question
I am trying to work out the best way to use the NameTable class in my C# application. I am assuming that getting/adding a string to the nametable has the same overheads as comparing a string normally, so I have created a class which holds references to the atomized strings. Is there a better way to do this? simple example below //Class to provide "string constants" private class MyStrings { public String stringA; public String stringB; public MyStrings(NameTable nt) { stringA = nt.Add("A"); stringB = nt.Add("B"); } } //Class...

Pick up date for Charts from the Spreadsheet
I have a workbook with one data sheet and several charts which represent weekly activity. I want to be able to pick up the title of the charts from the spreadsheet, e.g. "Sales Activity for Week Ending April 7, 2006". This title information is contained in a cell in the spreadsheet. How do I get it to display at the top of each chart without having to manually type it in the title area under Chart Options? Wilfred: Here' a link to a post I have on dynamic titles that sounds like what you are looking for. http://processtrends.com/pg_chart_dynamic_chart_title.htm I ...

Date formatting Help
Macro - Help How can you select a cell with a Sunday's Date to give you th following Sunday's Date when execute -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27551 To get next Sunday's date, just add 7 to this Sunday. So something like: cell.value + 7 -- Regards, Fred Please reply to newsgroup, not e-mail "Db1712" <Db1712.1f88gn@excelforum-nospam.com> wrote in message ...

Custom Toolbar Button Question
Hello , I have a GP 9 and I would like to create a custom toolbar button that executes some Dex code. I need to be able to access the data on the current form because I want to invoke an a third party api using some of the field values as variables to pass into the third party api as parameters. I have the dex code for the third party api working fine but I was wondering if someone could point me to a sample. What content objects are available? Thanks, gsvi HI, I have done this before and once you master the concept it's not too hard. It is very close to implementing menu command...

Countif accross multiple questions.
Hi folks, I know this has been asked and answered, but I could not get it working. I thought I would ask my specific problem. I have a excel spreadsheet with a detail sheet containing columns. One column is Inventoried whose values are TRUE or FALSE. Another column is PRICE which could be 0 or any number like 5.35. In anoher sheet I want a summary. I have one cell that is inventoried (=COUNTIF('INVENTORY'!E:E,"TRUE" I need another Cell that will be Unpriced inventory which is the number if Inventoried = 'TRUE' and price = 0. I tried the sumproduct but keep get...

Question about clearing out CRM, and re-migrating with Scribe??
We have been testing the migration from Goldmine 6.5 Corporate, into CRM 3.0 using Scribe Insight. Everything is working "ok" but we are having some minor issues. Now we want to completely empty out the CRM databases, and test another migration with Scribe. Is there something special that i need to do for this to happen? I am hoping i dont have to uninstall/reinstall. Thanks Rodney Hi Rodney, What entities did you use Scribe to migrate? What entities did you use in CRM? you might able to clear the data out using a SQL script. Darren Liu Crowe Chizek and Company http://www.cr...

Dates in Charts
I have a chart that I have created based on data that lists dates and amounts. The dates are not consecutive however when I create the chart it shows all dates March 1, March 2, March 3 Even though I did not enter March 2 at all. How do I stop it from doing that? you cant. just like if you had numeric data 0, 3, 9, 15 and plotted it. the numbers in between are still shown on the axis. AGP "Erika" <Erika@discussions.microsoft.com> wrote in message news:4BC1E855-7741-44A3-9E5F-7E928A16AE15@microsoft.com... >I have a chart that I have created based on data that lists ...

SUMIF Question #9
I used the SUMBYCOLOR option, but it still came back #NAME? maybe I a doing something wrong, but the text is red and the range of cells i A1:A10, so I used the function =SUMBYCOLOR(A1:A10,3,FALSE) and I tried it with spaces betwee commas...do these formulas not work with Excel 2000? Or maybe I a just really confuse -- mksullv ----------------------------------------------------------------------- mksullvn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1509 View this thread: http://www.excelforum.com/showthread.php?threadid=26728 Hi you first have to put t...

How would you manage these dates?
I'm back, after posting just a couple of hours ago about "old" dates and getting great help. Now I know more about what my friend is trying to do, and I'm intrigued by the problem and am wondering how a real clever expert, like in this group, would handle it. She's archiving historical letters and using an Excel file. So she's got rows with the topic, who wrote it, who received it, the date, etc. Let's ignore the problem of "old" dates now and assume they're all after 1900. Here's the tricky part: she's not sure about some dates. Thi...

MSDN Evaluation => Full license Question
Just a quick question today. We are currently evaluating CRM through MSDN licenses and will wish to buy pro- licenses for everyone. The evaluation is taking place on what will be the production server/active directory will remain the same. When we upgrade, will we have to completely reinstall and perform a data migration from CRM to CRM? Do all pieces need to be reinstalled (i.e. Exchange router??) I think we do, but I've heard conflicting info and wanted to run it past the experts... Yes, you will need to reinstall. The license is tied to the Org Name so you won't be abl...

Need to automaticallu input date in form
This seems to be a very popular question posed on the forum, and I have read through and tried the most viable solutions given to others. I have three diferent forms that update a table with three different date fields. Although I have set the default value for all the date fields in the table to =Date()- 1, this only works on one of the forms, even though that field in each form is given same properties. The forms are used to update an inventory table containing part numbers, wip dates and amounts, packing dates and amounts, and shipping dates and amounts. I use the three different forms bec...

Important question about CRM Architecture
Do exchange server and crm server has to be in the same domain or only in the same forest ? Tnks in advance Only in the same forest (or (probably) in trusted domains from other forests) "Karolina" <Karolina@discussions.microsoft.com> wrote in message news:FB033B64-A9C6-4146-9CBC-9CFA945EE1D6@microsoft.com... > > Do exchange server and crm server has to be in the same domain or only in > the same forest ? > > Tnks in advance ...

OT: PE file format question
I know this is off topic but I hope someone can at least send me somewhere I can get this question answered. I am mucking with an executible file. I have change the .rsrc section and I am fixing up all the RVAs in the sections that follow and in the headers. There is still some problem however. The document that I have that talks about the PE file format says there are RVAs in the .reloc section. I don't see any. It looks like the offsets are relative the the beginning of the code. So I'm somewhat confused. I'm guessing there are still some RVAs somewhere that need to be ad...

question.
how can i setup sheet to be like the bank rigstor can any one help me i am new to the computer world but i have to learn at some point thank you so much ace, start by doing a search here, http://office.microsoft.com/en-us/templates/default.aspx http://www.google.com/advanced_group_search?q=group:*excel*&lr=lang_en&num=50&hl=en -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "ace" <ace@discussions.microsoft....