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

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
184
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,057
Office Version
  1. 365
Platform
  1. Windows
When you use R1C1 formula's in VBA you need double quotes
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,057
Office Version
  1. 365
Platform
  1. Windows
In VBA

VBA Code:
ActiveCell = Int([g2] - [f2]) & " days " & Format([g2] - [f2], "h ""hrs"" m ""mins""")
 

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
692
Office Version
  1. 2016
Range("H2").Formula = "=INT(F2-E2) & "" days "" &TEXT(F2-E2,""h"""" hrs """"m"""" mins """""")"
 
Solution

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
184
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,175,887
Messages
5,900,073
Members
434,817
Latest member
nbretscher13

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
Top