Problem With VBA for Calculating Number of Days, Hours, and Minutes Between Two Dates/Times

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
206
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a formula that works well if I insert directly into a cell (Duration Past Clock Out):

=INT(F2-E2)&" days "&TEXT(F2-E2,"h"" hrs ""m"" mins """)

I get this output:

Employee IDActivity NameTran #Begin TimeClock Out TimeEnd TimeSupervisorDuration Past Clock Out
jsmithLM CLEAN476614878/22/2021 17:518/22/2021 18:018/28/2021 6:15OF_12A5 days 12 hrs 14 mins

But when I put it in a macro I have to format this data:

Range("H2").Select
ActiveCell.FormulaR1C1 = "=INT(F2-E2)&" days "&TEXT(F2-E2,"h"" hrs ""m"" mins """)"

I get:

1630151628056.png


Does anyone know what I'm doing incorrectly?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
When you use R1C1 formula's in VBA you need double quotes
 
Upvote 0
In VBA

VBA Code:
ActiveCell = Int([g2] - [f2]) & " days " & Format([g2] - [f2], "h ""hrs"" m ""mins""")
 
Upvote 0
Range("H2").Formula = "=INT(F2-E2) & "" days "" &TEXT(F2-E2,""h"""" hrs """"m"""" mins """""")"
 
Upvote 0
Solution
Thanks for the responses. I went with Phuoc's answer for the elegance of being able to eliminate the extra line of code I had been using to select the cell where I was putting the formula.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top