Jump to content
Search In
  • More options...
Find results that contain...
Find results in...

(Excel) IF statement for checking and printing certain times

RumSwift
 Share

Hey,

 

Working on a spreadsheet for use at work for hourly checks of specific tasks. Essentially I'm having an issue writing an if statement to check times and then print another time. Bad explanation, but I'll write it out for you:

 

There are two possible shift start times; 09:30 and 11:45. From there we have 8 checks.

 

If we start at 09:30 the checks look like this:

  • 10:30, 11:30 12:30, 13:30, 15:00, 15:30, 16:30 and 17:30

If we start at 11:45 the checks look like this:

  • 12:45, 13:45, 14:45, 15:30, 17:00. 17:45, 18:45 and 19:45

The checks I've highlighted are the checks that are done as a lunch break starts. Lunch is 45 minutes and then the next check after 90 minutes after (13:30 - 15:00 // 15:30 - 17:00).

 

Basically what I'm trying to do is write an if statement that checks the time before the lunch break check and then if that time is 12:30, it prints 13:30 for the dinner check and if it's 14:45 it prints 15:30 for the lunch break check. Having some trouble getting it to work. Just wondering if anyone could help me out.

 

Cells are formatted as custom and look like this:

 

Spoiler

image.png.101166ba34945d0fef351a5da144acc8.png

 

If this is really unclear let me know and I'll try to explain better. Should just be a simple check but I just can't get it to work ?.

Current Build:

CPU: AMD Ryzen 5 2600 // Mobo: Ryzen AM4 B350 GAMING PLUS ATX // RAM: 16GB Corsair Vengeance LPX DDR4 3000MHz // GPU: Gigabyte AMD Radeon RX 580 Gaming 8GB // SSD: Kingston A400 120GB // HDD: 3 x WD Blue 1TB // PSUCorsair 650M // Case: Corsair 450D // Monitor: LG Ultrawide 29" IPS

 

Plex Server:

CPU: AMD FX 8350 Black Edition // Mobo: Gigabyte - GA-78LMT-USB3 R2 Micro ATX // RAM: 16GB Corsair Vengeance DDR3 1600 MHz // GPU: GeForce GTX 670 // HDD: Seagate BarraCuda 2TB // PSU: Kolink Core Series 500W 80 Plus Certified // Case: AVP Viper Mini Tower

 

Other:

PS4 Pro // PS3 // Nintendo Switch (Pokemon edition) // Nintendo 3DS // Xbox 360 // iPhone 8 Plus // Macbook Retina 2013

Link to comment
Share on other sites

Link to post
Share on other sites

Do you want the Check 1 - 8 to be filled in depending on what the shift start is?

Link to comment
Share on other sites

Link to post
Share on other sites

1 minute ago, Dujith said:

Do you want the Check 1 - 8 to be filled in depending on what the shift start is?

Yeah, sorry, should have outlined that part. 

 

Just using a simple +1/24 to add an hour to each time along the way so far. But if there is an easier way to do it tat would be cool.

Current Build:

CPU: AMD Ryzen 5 2600 // Mobo: Ryzen AM4 B350 GAMING PLUS ATX // RAM: 16GB Corsair Vengeance LPX DDR4 3000MHz // GPU: Gigabyte AMD Radeon RX 580 Gaming 8GB // SSD: Kingston A400 120GB // HDD: 3 x WD Blue 1TB // PSUCorsair 650M // Case: Corsair 450D // Monitor: LG Ultrawide 29" IPS

 

Plex Server:

CPU: AMD FX 8350 Black Edition // Mobo: Gigabyte - GA-78LMT-USB3 R2 Micro ATX // RAM: 16GB Corsair Vengeance DDR3 1600 MHz // GPU: GeForce GTX 670 // HDD: Seagate BarraCuda 2TB // PSU: Kolink Core Series 500W 80 Plus Certified // Case: AVP Viper Mini Tower

 

Other:

PS4 Pro // PS3 // Nintendo Switch (Pokemon edition) // Nintendo 3DS // Xbox 360 // iPhone 8 Plus // Macbook Retina 2013

Link to comment
Share on other sites

Link to post
Share on other sites

Assuming there are only 2 shift start times and you want the cells changing depending on the content of the cell in the column D you can do:

=IF(D3="09:30:00","10:30:00","12:45:00")

EDIT: That is an example for Check 1.

CPU: Ryzen 7 5800X Cooler:  Corsair H100i Platinum SE Mobo: Asus B550-A Gaming GPU: EVGA RTX 2070 XC RAM: G.Skill Trident Z RGB 3200MHz 16CL 4x8GB SSD0: Crucial MX300 525GB SSD1: Samsung QVO 1TB PSU: NZXT C650 Case: Corsair 4000D Airflow Monitor: Asus VG259QM (240Hz)

I usually edit my posts immediately after posting them, as I don't check for typos before pressing the shiny SUBMIT button.

Spoiler

Unraid Server

CPU: Ryzen 5 1600 (3.6GHz; 1.25v) Cooler:  EVGA CLC 240mm Mobo: Asus Strix B350-i GPU: GT 1030 4GB RAM: G.Skill Trident Z 3200MHz 14CL 2x8GB SSD: ADATA SU800 128GB HDD: Seagate Ironwolf 2x6TB PSUCorsair SF450 Case: NZXT H200i

Link to comment
Share on other sites

Link to post
Share on other sites

5 minutes ago, Nocte said:

Assuming there are only 2 shift start times and you want the cells changing depending on the content of the cell in the column D you can do:


=IF(D3="09:30:00","10:30:00","12:45:00")

EDIT: That is an example for Check 1.

For copying ease add the dollar sign (that locks a letter or number of the row)

=IF($D3

That way it will always check column D and whatever row you are on

Link to comment
Share on other sites

Link to post
Share on other sites

8 minutes ago, Nocte said:

Assuming there are only 2 shift start times and you want the cells changing depending on the content of the cell in the column D you can do:


=IF(D3="09:30:00","10:30:00","12:45:00")

EDIT: That is an example for Check 1.

See this is what I've been doing, but for some reason it's not printing correctly. 

Current Build:

CPU: AMD Ryzen 5 2600 // Mobo: Ryzen AM4 B350 GAMING PLUS ATX // RAM: 16GB Corsair Vengeance LPX DDR4 3000MHz // GPU: Gigabyte AMD Radeon RX 580 Gaming 8GB // SSD: Kingston A400 120GB // HDD: 3 x WD Blue 1TB // PSUCorsair 650M // Case: Corsair 450D // Monitor: LG Ultrawide 29" IPS

 

Plex Server:

CPU: AMD FX 8350 Black Edition // Mobo: Gigabyte - GA-78LMT-USB3 R2 Micro ATX // RAM: 16GB Corsair Vengeance DDR3 1600 MHz // GPU: GeForce GTX 670 // HDD: Seagate BarraCuda 2TB // PSU: Kolink Core Series 500W 80 Plus Certified // Case: AVP Viper Mini Tower

 

Other:

PS4 Pro // PS3 // Nintendo Switch (Pokemon edition) // Nintendo 3DS // Xbox 360 // iPhone 8 Plus // Macbook Retina 2013

Link to comment
Share on other sites

Link to post
Share on other sites

6 minutes ago, Rhyss said:

See this is what I've been doing, but for some reason it's not printing correctly. 

What happens? can u show an example?

 

Nvm its Excel converting the cell to a time cell, just tested it.

The correct IF statement is:

=IF($D3=TIME(9;30;00);"10:30";"12:45")

Just remember to use semicolons in the TIME bracket :)  hours;minutes;seconds

Link to comment
Share on other sites

Link to post
Share on other sites

13 minutes ago, Dujith said:

What happens? can u show an example?

 

Nvm its Excel converting the cell to a time cell, just tested it.

The correct IF statement is:


=IF($D3=TIME(9;30;00);"10:30";"12:45")

Just remember to use semicolons in the TIME bracket :)  hours;minutes;seconds

Sorry had to shoot and do something.

 

Thanks so much for this!

Current Build:

CPU: AMD Ryzen 5 2600 // Mobo: Ryzen AM4 B350 GAMING PLUS ATX // RAM: 16GB Corsair Vengeance LPX DDR4 3000MHz // GPU: Gigabyte AMD Radeon RX 580 Gaming 8GB // SSD: Kingston A400 120GB // HDD: 3 x WD Blue 1TB // PSUCorsair 650M // Case: Corsair 450D // Monitor: LG Ultrawide 29" IPS

 

Plex Server:

CPU: AMD FX 8350 Black Edition // Mobo: Gigabyte - GA-78LMT-USB3 R2 Micro ATX // RAM: 16GB Corsair Vengeance DDR3 1600 MHz // GPU: GeForce GTX 670 // HDD: Seagate BarraCuda 2TB // PSU: Kolink Core Series 500W 80 Plus Certified // Case: AVP Viper Mini Tower

 

Other:

PS4 Pro // PS3 // Nintendo Switch (Pokemon edition) // Nintendo 3DS // Xbox 360 // iPhone 8 Plus // Macbook Retina 2013

Link to comment
Share on other sites

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share


×