VBA: calculate difference in hh:mm between "now"-"target date", excluding weekends

[Ron_N]

Hi,

Please assist. I am looking to write a VBA code.

I have a column named "target date" in mm/dd/yyyy 12 hour(AM/PM) format. for example 9/29/2015 5:20 pm.

I want to calculate the difference in hh:mm between "now"-"target date", excluding weekends. i want the resulting difference in hh:mm format.

please assist me. Thank you in advance.

 

[peet338]

Hi Ron,

A formula could do this (although it's not a pretty one) and as the number of hours would be greater than 24 you would need to output it as a text field.

the formula would be:

hours:

a) number of full work days between the start of today and the end of the target day (the NETWORKDAYS function excludes weekends and public holidays if specified) * 24 (assuming you want to recognise 24 hours in a day).

b) less the number of hours between the nominated target hour and midnight on the target date.

C) less the number of hours expired today (from midnight).

D) less one hour if the number of minutes expired in the current hour is greater than the number of minutes in the target date.

Minutes:

if the minute in the target timeis less than the current minute then

60 + minute of the target time- minute of the current time (the 60 minutes is subtracted from the number of hours in D above if required).

else

minute of the target time - minute of the current time.

then concatenate the hours and minute results.

The formula would be (assuming your target date and time is in cell "B2"):

=CONCATENATE(NETWORKDAYS(NOW(),$B$2)*24+HOUR($B$2)-24-HOUR(NOW())-IF(MINUTE(NOW())>MINUTE($B$2),1,0),":",IF(MINUTE(B2)<minute(now()),60+minute(b2)-minute(now()),minute(b2)-minute(now())))


Hope this helps and someone can come up with a better answer.

Thanks,

Pete</minute(now()),60+minute(b2)-minute(now()),minute(b2)-minute(now())))

 

[peet338]

Hi Ron,

Sorry the back end of the formula was cut off in the above:

'=CONCATENATE(NETWORKDAYS(NOW(),$B$2)*24+HOUR($B$2)-24-HOUR(NOW())
-IF(MINUTE(NOW())>MINUTE($B$2),1,0),":",IF(MINUTE($B$2)<minute(now())
',60+MINUTE($B$2)-MINUTE(NOW()),MINUTE($B$2)-MINUTE(NOW())))<minute(now()),60+minute($b$2)-minute(now()),minute($b$2)-minute(now())))<minute(now()),60+minute($b$2)-minute(now()),minute($b$2)-minute(now())))[ code]<minute(now()),60+minute($b$2)-minute(now()),minute($b$2)-minute(now())))<minute(now()),60+minute($b$2)-minute(now()),minute($b$2)-minute(now())))<minute(now()),60+minute($b$2)-minute(now()),minute($b$2)-minute(now())))[="" quote]<="" html=""></minute(now()),60+minute($b$2)-minute(now()),minute($b$2)-minute(now())))<minute(now()),60+minute($b$2)-minute(now()),minute($b$2)-minute(now())))[></minute(now())

 

[Ron_N]

<minute(now())
Hi Peet,

Thank you for your quick help.

I applied the formula. but Peet, i am getting only #VALUE!

I don't know where i am doing wrong. Could you tell me what could be the possible cause.

Thank you for your help.

Ron
</minute(now())

 

[peet338]

Hi Ron,

The first thing to check is that at the start of the second line of the formula above I've had to put in a ' to split it into two lines, you will need to delete that (sorry that's my fault).

If that doesn't work, check the format of your target date and make sure that's set correctly.

Cheers,

Pete

 

[sanrv1f]

Assuming the target date is in future

=A4-NOW()-(INT(A4)-TODAY()-NETWORKDAYS(TODAY(),A4)+1)

If it is in the past, just swap Now() & target cell

 

[Ron_N]

HI Pete,

Thank you for all your help.
The format of my Target date column is for example 10/2/2015 5:30:00 PM in column B.
I am getting error. I know there would be a minor correction to it. but i am not able to figure it out.

This is the code i am using

Sub Aging()
Application.ScreenUpdating = False
' Renames the active sheet to Data
Sheets("Data").Select
ActiveWorkbook.ActiveSheet.Name = "Data"

' Inserting a column for Aging days formula
Columns("g:g").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("g1").Select
ActiveCell.FormulaR1C1 = "Time Left"


' Applying difference in time formula ' This loop repeats for a fixed number of times determined by the number of rows in the range
Range("G2").Select
Dim kk As Integer
For kk = 1 To Selection.CurrentRegion.Rows.Count - 1
ActiveCell.FormulaR1C1 = "=CONCATENATE(NETWORKDAYS(NOW(),$B$2)*24+HOUR($B$2)-24-HOUR(NOW())"
-IF(MINUTE(NOW())>MINUTE($B$2),1,0),":",IF(MINUTE($B$2) ,60+MINUTE($B$2)-MINUTE(NOW()),MINUTE($B$2)-MINUTE(NOW())))"
ActiveCell.Offset(1, 0).Select
Next kk
On Error Resume Next

Application.ScreenUpdating = True
End Sub

 

[Ron_N]

Pete and Shankar,

I sincerely thank you for all the help provided. You rock.