MAx Date and Time

vmjan02 · Nov 1, 2023

I have this column and nee dto find the max date and time in one function is there a way there may will be woth date and time,
any other function

TASK LAST UPDATE DATE

9/1/2023 21:11

9/1/2023 18:40

9/1/2023 15:01

9/1/2023 21:11

9/2/2023 14:46

9/4/2023 21:43

9/1/2023 19:47

9/2/2023 15:30

9/1/2023 21:57

9/3/2023 15:51

9/1/2023 18:53

9/1/2023 14:56

9/3/2023 15:33

9/2/2023 18:00

9/2/2023 16:35

9/3/2023 10:25

9/1/2023 21:11

9/4/2023 15:25

9/1/2023 20:24

9/1/2023 13:45

9/1/2023 21:11

9/1/2023 2:23

9/1/2023 21:11

9/1/2023 13:08

9/1/2023 19:10

9/1/2023 0:57

9/3/2023 11:01

9/1/2023 16:46

9/2/2023 20:17

9/1/2023 21:11

9/3/2023 11:13

9/1/2023 21:05

9/1/2023 15:10

9/2/2023 15:23

9/1/2023 20:58

9/5/2023 14:37

9/1/2023 17:57

9/1/2023 20:11

9/1/2023 20:29

9/1/2023 21:11

9/1/2023 21:08

9/1/2023 17:12

9/1/2023 20:02

9/1/2023 21:08

Asbestos_Jen · Nov 1, 2023

Are you trying to achieve this? 9/3/23 10:25 (subtract something) = 9/2/2023 9:15

Then you need to subtract one day and one hour and 10 minutes. =MAX(A2:A48)-(1+(1/24)+(1/60/24))

Scott Huish · Nov 1, 2023

With your data, that is not what is happening. I put a MAX function, and a MAX -1. How is this not what you are expecting?

Book1

A

B

1

TASK LAST UPDATE DATE

9/5/2023 14:37

2

9/1/2023 21:11

9/4/2023 14:37

3

9/1/2023 18:40

4

9/1/2023 15:01

5

9/1/2023 21:11

6

9/2/2023 14:46

7

9/4/2023 21:43

8

9/1/2023 19:47

9

9/2/2023 15:30

10

9/1/2023 21:57

11

9/3/2023 15:51

12

9/1/2023 18:53

13

9/1/2023 14:56

14

9/3/2023 15:33

15

9/2/2023 18:00

16

9/2/2023 16:35

17

9/3/2023 10:25

18

9/1/2023 21:11

19

9/4/2023 15:25

20

9/1/2023 20:24

21

9/1/2023 13:45

22

9/1/2023 21:11

23

9/1/2023 21:11

24

9/1/2023 2:23

25

9/1/2023 21:11

26

9/1/2023 21:11

27

9/1/2023 13:08

28

9/1/2023 19:10

29

9/1/2023 0:57

30

9/3/2023 11:01

31

9/1/2023 16:46

32

9/2/2023 20:17

33

9/1/2023 21:11

34

9/1/2023 21:11

35

9/3/2023 11:13

36

9/1/2023 21:05

37

9/1/2023 15:10

38

9/2/2023 15:23

39

9/1/2023 20:58

40

9/5/2023 14:37

41

9/1/2023 17:57

42

9/1/2023 20:11

43

9/1/2023 20:29

44

9/1/2023 21:11

45

9/1/2023 21:08

46

9/1/2023 17:12

47

9/1/2023 20:02

48

9/1/2023 21:08

Sheet1

Cell Formulas
Range	Formula
B1	B1	=MAX(A2:A48)
B2	B2	=MAX(A2:A48)-1

vmjan02 · Nov 2, 2023

Scott Huish said:
With your data, that is not what is happening. I put a MAX function, and a MAX -1. How is this not what you are expecting?

Book1
A B
1 TASK LAST UPDATE DATE 9/5/2023 14:37
2 9/1/2023 21:11 9/4/2023 14:37
3 9/1/2023 18:40
4 9/1/2023 15:01
5 9/1/2023 21:11
6 9/2/2023 14:46
7 9/4/2023 21:43
8 9/1/2023 19:47
9 9/2/2023 15:30
10 9/1/2023 21:57
11 9/3/2023 15:51
12 9/1/2023 18:53
13 9/1/2023 14:56
14 9/3/2023 15:33
15 9/2/2023 18:00
16 9/2/2023 16:35
17 9/3/2023 10:25
18 9/1/2023 21:11
19 9/4/2023 15:25
20 9/1/2023 20:24
21 9/1/2023 13:45
22 9/1/2023 21:11
23 9/1/2023 21:11
24 9/1/2023 2:23
25 9/1/2023 21:11
26 9/1/2023 21:11
27 9/1/2023 13:08
28 9/1/2023 19:10
29 9/1/2023 0:57
30 9/3/2023 11:01
31 9/1/2023 16:46
32 9/2/2023 20:17
33 9/1/2023 21:11
34 9/1/2023 21:11
35 9/3/2023 11:13
36 9/1/2023 21:05
37 9/1/2023 15:10
38 9/2/2023 15:23
39 9/1/2023 20:58
40 9/5/2023 14:37
41 9/1/2023 17:57
42 9/1/2023 20:11
43 9/1/2023 20:29
44 9/1/2023 21:11
45 9/1/2023 21:08
46 9/1/2023 17:12
47 9/1/2023 20:02
48 9/1/2023 21:08
Sheet1
Cell Formulas
Range Formula
B1 B1 =MAX(A2:A48)
B2 B2 =MAX(A2:A48)-1

If we -1 day then it suld take the day and the time from the table

Book1

A

B

C

D

E

F

1

TASK LAST UPDATE DATE

9/5/2023 14:37

correct

2

9/1/2023 21:11

9/4/2023 14:37

-1

9/4/2023 21:43

This shuld be the result

3

9/1/2023 18:40

4

9/1/2023 15:01

5

9/1/2023 21:11

6

9/2/2023 14:46

7

9/4/2023 21:43

8

9/1/2023 19:47

9

9/2/2023 15:30

10

9/1/2023 21:57

11

9/3/2023 15:51

12

9/1/2023 18:53

13

9/1/2023 14:56

14

9/3/2023 15:33

15

9/2/2023 18:00

16

9/2/2023 16:35

17

9/3/2023 10:25

18

9/1/2023 21:11

19

9/4/2023 15:25

20

9/1/2023 20:24

21

9/1/2023 13:45

22

9/1/2023 21:11

23

9/1/2023 21:11

24

9/1/2023 2:23

25

9/1/2023 21:11

26

9/1/2023 21:11

27

9/1/2023 13:08

28

9/1/2023 19:10

29

9/1/2023 0:57

30

9/3/2023 11:01

31

9/1/2023 16:46

32

9/2/2023 20:17

33

9/1/2023 21:11

34

9/1/2023 21:11

35

9/3/2023 11:13

36

9/1/2023 21:05

37

9/1/2023 15:10

38

9/2/2023 15:23

39

9/1/2023 20:58

40

9/5/2023 14:37

41

9/1/2023 17:57

42

9/1/2023 20:11

Sheet1

Cell Formulas
Range	Formula
C1	C1	=MAX(A1:A42)
C2	C2	=MAX(A1:A42)-1

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$A$42	C1:C2

Phuoc · Nov 2, 2023

Try this in C2:

=LARGE(A2:A42,2)

vmjan02 · Nov 2, 2023

Asbestos_Jen said:
Are you trying to achieve this? 9/3/23 10:25 (subtract something) = 9/2/2023 9:15

Then you need to subtract one day and one hour and 10 minutes. =MAX(A2:A48)-(1+(1/24)+(1/60/24))

Phuoc said:
Try this in C2:

=LARGE(A2:A42,2)

large don't work,
I tried

VBA Code:

=VLOOKUP((MAX('All Ticket'!W:W)-1),'All Ticket'!W:W,1,0)

but no luck

Phuoc · Nov 2, 2023

Try again, in C2:

=AGGREGATE(14,6,A2:A42/(INT(A2:A42)<INT(MAX(A2:A42))),1)

awoohaw · Nov 2, 2023

Can you just show the arithmetic for how you get:
(9/4/2023 14:37) -1 = 9/4/2023 21:43

MAx Date and Time

vmjan02

Well-known Member

Asbestos_Jen

Active Member

Excel Facts

Scott Huish

MrExcel MVP

vmjan02

Well-known Member

Phuoc

Well-known Member

vmjan02

Well-known Member

Phuoc

Well-known Member

awoohaw

Well-known Member

Similar threads

Forum statistics

Share this page

MAx Date and Time

Well-known Member

Active Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock