How to reference Time in a Date/Time Field

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625
Hi
Column F & G are date/time fields formatted as 3/14/12 1:30 PM . In Column H I want an if statement that says if the time portion of F or G is less than 7:00 AM bump the time up to 7:00 AM else let it be what it is

Example
F H
7/21/2020 7:39:09 AM 7/21/2020 7:39:09 AM H would remain as 7/21/2020 7:39:09 AM
7/22/2020 3:31:39 AM 7/22/2020 7:00:00 AM H would become 7/22/2020 7:00:00 AM

I was trying to use the Mod(F2,1) function If (Mod(f2,1) < 7:00:00 AM, int(f2) & 7:00:00 AM,F2) but it is obviously wrong

Thanks
Liz
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,513
Office Version
  1. 365
Platform
  1. Windows
Try this formula, for an entry in row 2:
Excel Formula:
=IF(F2-INT(F2)>(7/24),F2,INT(F2)+(7/24))
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625
Thank you both they are both working. Thanks so very much

Liz
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
625
Thank you both they are both working. Thanks so very much

Liz
 

Watch MrExcel Video

Forum statistics

Threads
1,119,182
Messages
5,576,569
Members
412,733
Latest member
korayuyanik
Top