TIME between two dates - want to return blank if empty and use today if end date is empty

Hi

I have a sheet where i need to calculate the time between a job start date and end date. If the end date isnt entered yet i want it to use todays date as the end date, and i want it to return blanks where neither start date or end date have yet been entered.

=IF(OR(ISBLANK(H4),ISBLANK(G4)),"",DATEDIF(G4,H4,"M")&"M "&DATEDIF(G4,H4,"MD")&"D")

G is the start date and H the end date. I have it returning in months and days.

Thanks to anyone who has a look at this - I find getting excel to return blanks the hardest thing!!

Thanks in Advance

You can do it like this:

=IF(H4&G4="","",DATEDIF(G4,IF(H4="",TODAY(),H4),"M")&"M "&DATEDIF(G4,IF(H4="",TODAY(),H4),"MD")&"D")

Hope this helps.

Pete

Try: =IF(OR(G4="",AND(ISBLANK(H4),ISBLANK(G4))),"",IF(H4="",DATEDIF(G4,TODAY(),"M")&"M "&DATEDIF(G4,TODAY(),"MD")&"D",DATEDIF(G4,H4,"M")&"M "&DATEDIF(G4,H4,"MD")&"D"))

Slight modification to Pete's formula (which is much neater) so it will return blank if no start date is entered.

=IF(OR(G4="",H4&G4=""),"",DATEDIF(G4,IF(H4="",TODAY(),H4),"M")&"M "&DATEDIF(G4,IF(H4="",TODAY(),H4),"MD")&"D")

Thanks for that I really appreciate it. But the formula is still returning an error message when the end date is missing?

where the end date is missing i get a #NUM! error - thanks ORoos

This could be if the start date is greater than the end date. If you leave the end date blank and the start date is in the future, then you get the #NUM error.

Hi ORoos

I have checked that and the start dates are in 2019 so it definately not that. The ones with an end date are returning perfectly - its just the ones missing end dates.

Please attach a sample workbook showing the errors. The procedure for doing this is given in the yellow banner heading above.

Pete

Hi Pete and ORoss = I put it in to a fresh spreadsheet to upload a sample and it worked! So i think there is an issue with my test spreadsheet.

Thanks every so much - you guys are great!!

HiDrOC10,
Glad to hear all if fine now.
If all is working now, please mark the thread as SOLVED.