Using IF functions to ignore date/time cells with missing date or time

**Custhasno** · 01-07-2019, 09:12 AM

I have a table with some date/time cells "DD/MM/YYYY HH:MM". I am using the following formula to ensure that when a date without a time is recorded (A5 in the example below) the system uses the data in Column B instead "=IF(TIME(HOUR(A1),MINUTE(A1),0),A1,B1)"

This solves the issue with returning #Value! when I am trying to work out the duration's of these jobs (when the time data is missing from the start of the job). I am now looking for a way (as part of the same formula) to take the data in Column B if the data in A does not have a date either (example in A7)? I'm just a little lost on the syntax of such a formula. So for example a call in A contains "DD/MM/YYYY" only or "HH:MM" only i wish to direct it to the same cell in B when displaying the duration times using one formula if that's possible?

____________A__________________B
____Orig Start DateTime_____Completed DateTime
1____01/09/2018 18:58____01/09/2018 19:58
2____01/09/2018 15:50____01/09/2018 15:57
3____01/09/2018 15:20____01/09/2018 15:26
4____01/09/2018 13:13____01/09/2018 13:21
5____01/09/2018_________01/09/2018 01:00
6____01/09/2018 06:25____01/09/2018 06:28
7____10:44______________01/09/2018 10:54

Sorry I just noticed the message about cross posting being discouraged. This was also posted here https://www.mrexcel.com/forum/excel-...ml#post5202977 but that was a few weeks ago to get help with the formula above. Once i realised i need to cover both missing time and missing date i added a comment but i am unsure of the response (or how to integrate it with the existing formula) and the guy is offline. In my panic i have come here.

**XLent** · 01-07-2019, 10:22 AM

Perhaps

Formula:

Please Login or Register  to view this content.

**Custhasno** · 01-07-2019, 10:54 AM

A very elegant looking solution thanks. it doesn't seem to be picking up the absence of dates in the date/time cell though. It is still showing a #VALUE! for the 7th row (time with no date).

**XLent** · 01-07-2019, 11:53 AM

Hmm, that is peculiar, if you explicitly coerce?

Formula:

Please Login or Register  to view this content.

If the above does not work please try and post a sample file (use GoAdvanced --> Manage Attachments), as that may highlight formatting issues etc....

**Custhasno** · 01-07-2019, 12:13 PM

Thanks, i have attached an example file. The second formula you included wont sit as a formula when i change the cells and paste it in it shows as text but Im using the = correctly.

For reference the cells in red are examples of partial data Most are missing the time and one is missing the date, the cells in bold are the formulas that deal with them.

**XLent** · 01-07-2019, 12:24 PM

thanks; switched to TRIM

Formula:

Please Login or Register  to view this content.

premise for above is that dates are integers, and time decimal (hour being 1/24) -- so above just checks if Integer is 0 (no date), or is devoid of decimal (no time)

**Custhasno** · 01-07-2019, 12:36 PM

It's all sufficiently advance for me to seem like magic, thank you kindly this works perfectly.

Edit: When i get a blank cell instead of a partial one is there anyway to get that to also trigger the formula to take the date from another cell instead? They are currently showing as #Value! ?

**XLent** · 01-07-2019, 01:30 PM

Hi, apologies for belated reply - away from my machine; would below work for you?

Formula:

Please Login or Register  to view this content.

**Custhasno** · 01-07-2019, 01:32 PM

Originally Posted by XLent

Hi, apologies for belated reply - away from my machine; would below work for you?

Formula:

Please Login or Register  to view this content.

I away from my machine now until tomorrow but thanks in advance

Edit: This works perfectly, thank you soo much.

**Custhasno** · 01-08-2019, 07:01 AM

The help so far has been excellent, unfortunately I need to ask for a little bit more help if possible?

Is there anyway the formula could also ignore both cells if they are blank and go to a 3rd row instead? I am now left with date/time of 00/01/1900 for any cells i apply this formula to if both are blank

**XLent** · 01-08-2019, 07:07 AM

Third row, or third cell?

In short, yes, you could have an IF around G2 however, if you could provide specifics we can tailor to your exact requirement(s) -- there may be a more elegant solution available that would iterate a range to find last valid datetime etc.

**Custhasno** · 01-08-2019, 07:23 AM

Originally Posted by XLent

Third row, or third cell?

In short, yes, you could have an IF around G2 however, if you could provide specifics we can tailor to your exact requirement(s) -- there may be a more elegant solution available that would iterate a range to find last valid datetime etc.

Thanks, ill try my best, could you give me an example with the IF G included, I'm ok once i have one to reverse engineer lol.

The file is that cumbersome I'm not even sure how to get just the relevant info for a simple file.

The data is essentially the time info for all repair jobs entered on to our system by our users. We have times for "job entered", "requested time", "Time started" and "Time completed" for any job we are asked to carry out across a large site. Because we are a 24/7 service we have kept date/time data combined "DD/MM/YYYY HH:MM" to allow for working out of durations when the jobs goes over in to the next day.

The next issue was that some rows where missing one or more of those pieces of data so some cells had only times HH:MM and other had only dates DD/MM/YYYY. The solution was to go to the next time, so if there was no allocation time recorded we take the allocation time as the start time of the jobs instead.

The final piece of the puzzle is to try to cope with the rarer occurrence of both cells in the formula being blank. IN that scenario i would like to point it to another cell (next row over)

The big sticking point is i have no control over the raw data i get, its outputted automatically and I am trying to build a reporting template in to which i can simply dump the raw data and have these formulas deal with any scenario of partial or missing data in this is hopefully the last piece of the puzzle.

**XLent** · 01-08-2019, 07:50 AM

Assuming you don't need to validate G in the same way you do F (i.e. will always be either blank or legitimate value and never time only etc) then

Formula:

Please Login or Register  to view this content.

However, if you need to apply similar logic against G as F, and potentially the "alternate" (should it be invoked) you would need to adjust the above. Again, this can be done, but it would be good to know what the alternate logic will be, whether it requires pre-emptive validation, and whether or not the "iterations" should continue until such time as a valid value is found.

**Custhasno** · 01-08-2019, 08:06 AM

Thanks i am trying that now.

So to try to explain better i need the formula to spot when a date/time is not correct and then use the next available date/time one row over for working out the durations at each stage.
So jobs go in the following order
Entered Time- when they are put on the system by a user
Requested time - Set to same time as entered unless changed to a future time by the user
Allocated time - when the job is sent to a workers handheld
Start - When that worker accepts/starts the jobs
Finish - When a worker completes the job

So if for example the system didn't capture the allocation time, i want the formula to take the start time (provided its not blank) and use that as the allocation time. The columns with this info are not all in a row.

**Custhasno** · 01-08-2019, 08:16 AM

This is working perfectly now i can't thank you enough

Using IF functions to ignore date/time cells with missing date or time

LinkBack

Thread Tools

Rate This Thread

Display

Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Re: Using IF functions to ignore date/time cells with missing date or time

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculating Elapse time from Dates with one date missing

[SOLVED] My date/Time conundrum or am I missing a simple trick?

Calculating total time between start date\time and End date\time excluding non business

Formula to Calculate Total time spent between Start Date & Time and End Date & Time

Add missing Time in cells with a date

I have a long time series (date and time) that has values missing. How can I insert them?

Calculating days & time left from start date/time to end date/time

Bookmarks

Bookmarks

Posting Permissions