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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,443
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,118,703
Messages
5,573,712
Members
412,548
Latest member
wallisonlac
Top