Home

Excel how to calculate a date with different criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-667464%22%20slang%3D%22en-US%22%3EExcel%20how%20to%20calculate%20a%20date%20with%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667464%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20assist%20with%20another%20query%20please.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20start%20date%20and%20i%20want%20to%20add%20a%20deadline%20date%20for%20it%20looking%20at%20text%20as%20a%20driver%20for%20the%20date.%26nbsp%3B%20As%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA3%20is%20the%20start%20date%20and%20in%20B3%20i%20want%20to%20put%20a%20due%20date%20-%20this%20needs%20to%20look%20at%20text%20in%20another%20column%20so%20it%20the%20column%20says%20Major%20overdue%20or%20Major%20Project%20it%20needs%20to%20have%20a%20deadline%20of%2025%20days%20from%20the%20start%20date%20and%20if%20the%20texts%20says%20Delay%20overdue%20or%20delay%20project%20i%20need%20the%20deadline%20date%20to%20be%2025%20days%20from%20the%20start%20date.%26nbsp%3B%20Hope%20this%20makes%20sense%20and%20thanks%20for%20any%20assistance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESue%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-667464%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667484%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20how%20to%20calculate%20a%20date%20with%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354261%22%20target%3D%22_blank%22%3E%40SPearson1973%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApologies%20Major%20needs%20to%20be%2030days%20and%20delay%2025days!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667736%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20how%20to%20calculate%20a%20date%20with%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354261%22%20target%3D%22_blank%22%3E%40SPearson1973%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20using%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DIF(ISNUMBER(SEARCH(%22major%22%2CC3))%2CA3%2B30%2CIF(ISNUMBER(SEARCH(%22delay%22%2CC3))%2CA3%2B25%2C%22%22))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117121i448A2D981DEE8D02%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Due%20Date.png%22%20title%3D%22Due%20Date.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20the%2030%20or%2025%20days%20to%20be%20only%20working%20days%20(excluding%20the%20weekends)%2C%20please%20check%20out%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DIF(ISNUMBER(SEARCH(%22major%22%2CC3))%2CWORKDAY(A3%2C30)%2CIF(ISNUMBER(SEARCH(%22delay%22%2CC3))%2CWORKDAY(A3%2C25)%2C%22%22))%26nbsp%3B%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117122iC7438C579FEAB41E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Due%20Date%202.png%22%20title%3D%22Due%20Date%202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
SPearson1973
New Contributor

Can someone assist with another query please. 

 

I have start date and i want to add a deadline date for it looking at text as a driver for the date.  As follows:

 

A3 is the start date and in B3 i want to put a due date - this needs to look at text in another column so it the column says Major overdue or Major Project it needs to have a deadline of 25 days from the start date and if the texts says Delay overdue or delay project i need the deadline date to be 25 days from the start date.  Hope this makes sense and thanks for any assistance

 

Sue

2 Replies

@SPearson1973 

Apologies Major needs to be 30days and delay 25days!

@SPearson1973

 

Hi,

 

I suggest using this formula:

=IF(ISNUMBER(SEARCH("major",C3)),A3+30,IF(ISNUMBER(SEARCH("delay",C3)),A3+25,""))

Due Date.png

 

If you want the 30 or 25 days to be only working days (excluding the weekends), please check out this formula:

=IF(ISNUMBER(SEARCH("major",C3)),WORKDAY(A3,30),IF(ISNUMBER(SEARCH("delay",C3)),WORKDAY(A3,25),"")) 

Due Date 2.png

 

Hope that helps

Related Conversations