如何在Google表格中基于其他列计算服药日期列？

Alright, let's work through your medication tracking Google Sheet setup. You've got a dropdown in Column A for your meds, variable first dose times based on when you wake up/eat breakfast, and need to calculate the next dose based on 6-hour or 24-hour intervals. Here's how to make this work smoothly:

The key here is to tie the first dose time (your starting point) directly to the interval assigned to each medication. Since your first dose time varies day-to-day, we'll build a formula that checks which drug is selected and adds the correct time interval to your initial dose time.

First, let's align on a typical sheet structure to match the formula logic:

• Column A: Medication Name (dropdown options: e.g., "6-Hour Interval Drug", "24-Hour Interval Drug")
• Column B: First Dose Time (manual entry — can be just a time like 8:30 or a full date-time like 2024/05/20 08:30; Google Sheets handles both seamlessly)
• Column C: Next Dose Time (where we'll add our calculation formula)

Formula for Next Dose Calculation #

In cell C2 (assuming your first row of actual data is row 2), use this IF statement to handle both interval types:

=IF(A2="6-Hour Interval Drug", B2 + TIME(6, 0, 0), IF(A2="24-Hour Interval Drug", B2 + TIME(24, 0, 0), ""))

If you prefer a cleaner, more readable syntax (and your Google Sheets supports it), swap in the SWITCH function instead:

=SWITCH(A2, "6-Hour Interval Drug", B2 + TIME(6, 0, 0), "24-Hour Interval Drug", B2 + TIME(24, 0, 0), "")

Just drag this formula down Column C to apply it to all your rows.

Quick Formatting Tip #

Make sure Column C is formatted as Date time (go to Format > Number > Date time) so the result displays both the date and time clearly — this is especially helpful if your next dose crosses into the next day.

If you want to calculate subsequent doses (e.g., third, fourth, etc.), just reference the previous dose time. For example, in cell D2 (second next dose), use:

=IF(A2="6-Hour Interval Drug", C2 + TIME(6, 0, 0), IF(A2="24-Hour Interval Drug", C2 + TIME(24, 0, 0), ""))

Drag this down and it'll keep calculating based on the prior dose time automatically.

内容的提问来源于stack exchange，提问作者Whadaya W Tinkin